如何在SQL Server 2008中的存储过程内编写游标

I have two tables in my database

我的数据库中有两个表

Coupon Table

优惠券表

id (int)
id（int）
Name (nvarchar(max))
名称（nvarchar（max））
NoofUses (int)
NoofUses（int）

CouponUse Table

优惠券表

id(int)
ID（INT）
Couponid(int)
Couponid（INT）
CreateDate(datetime)
CREATEDATE（日期时间）

Whenever a user clicks on a coupon an entry goes into the CouponUse table containing that Coupon's id

每当用户点击优惠券时，条目就会进入包含该优惠券ID的CouponUse表

Now there is a column in the coupon table called NoofUses. I want to write a cursor inside a stored procedure which loops over couponuse table and sees how many rows are there for one coupon and fill that number in NoofUses field in coupon.

现在优惠券表中有一个名为NoofUses的列。我想在存储过程中写一个游标，它循环在couponuse表上，并查看一个优惠券有多少行，并在优惠券的NoofUses字段中填写该数字。

I have this query

我有这个问题

  select COUNT(*) as totalcount , Name as name from Coupon as coupon 
  join CouponUse as couponuse on coupon.id = couponuse.couponid
  group by couponuse.couponid , coupon.Name

which gives me the coupon name and its count from couponuse

它给了我优惠券的优惠券名称和数量

But I don't know how to implement that in a stored procedure using a cursor ?

但我不知道如何使用游标在存储过程中实现它？

Anything you ask about question will be appreciated , Thanks

任何你问的问题将不胜感激，谢谢

3 个解决方案

#1

What's wrong with just simply using a single, simple UPDATE statement??

只使用一个简单的UPDATE语句有什么问题？

UPDATE dbo.Coupon
SET NoofUses = (SELECT COUNT(*) FROM dbo.CouponUse WHERE Couponid = dbo.Coupon.ID)

That's all that's needed ! No messy and complicated cursor, no looping, no RBAR (row-by-agonizing-row) processing ..... just a nice, simple, clean set-based SQL statement.

这就是所有需要的！没有杂乱和复杂的游标，没有循环，没有RBAR（逐行激动行）处理.....只是一个漂亮，简单，干净的基于集合的SQL语句。

#2

Try the following snippet. You can call the the below stored procedure from your application, so that NoOfUses in the coupon table will be updated.

请尝试以下代码段。您可以从应用程序中调用以下存储过程，以便更新优惠券表中的NoOfUses。

CREATE PROCEDURE [dbo].[sp_UpdateCouponCount]
AS

Declare     @couponCount int,
            @CouponName nvarchar(50),
            @couponIdFromQuery int


Declare curP cursor For

  select COUNT(*) as totalcount , Name as name,couponuse.couponid  as couponid from Coupon as coupon 
  join CouponUse as couponuse on coupon.id = couponuse.couponid
  where couponuse.id=@cuponId
  group by couponuse.couponid , coupon.Name

OPEN curP 
Fetch Next From curP Into @couponCount, @CouponName,@couponIdFromQuery

While @@Fetch_Status = 0 Begin

    print @couponCount
    print @CouponName

    update Coupon SET NoofUses=@couponCount
    where couponuse.id=@couponIdFromQuery


Fetch Next From curP Into @couponCount, @CouponName,@couponIdFromQuery

End -- End of Fetch

Close curP
Deallocate curP

Hope this helps!

希望这可以帮助！

#3

You can create a trigger which updates NoofUses column in Coupon table whenever couponid is used in CouponUse table

您可以创建一个触发器，只要在CouponUse表中使用couponid，就会更新Coupon表中的NoofUses列

query :

查询：

CREATE TRIGGER [dbo].[couponcount] ON [dbo].[couponuse]
FOR INSERT
AS
if EXISTS (SELECT 1 FROM Inserted)
  BEGIN
UPDATE dbo.Coupon
SET NoofUses = (SELECT COUNT(*) FROM dbo.CouponUse WHERE Couponid = dbo.Coupon.ID)
end

#1