如何在 SQL Server 2008 中的存储过程中编写游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13507317/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to write a cursor inside a stored procedure in SQL Server 2008
提问by Smartboy
I have two tables in my database
我的数据库中有两个表
Coupon Table
优惠券表
- id (int)
- Name (nvarchar(max))
- NoofUses (int)
- 身（整数）
- 名称 (nvarchar(max))
- NoofUses (int)
CouponUse Table
优惠券使用表
- id(int)
- Couponid(int)
- CreateDate(datetime)
- 身（整数）
- 优惠券（整数）
- 创建日期（日期时间）
Whenever a user clicks on a coupon an entry goes into the CouponUsetable containing that Coupon's id
每当用户点击优惠券时，都会有一个条目进入CouponUse包含该优惠券 ID的表格
Now there is a column in the coupontable called NoofUses. I want to write a cursor inside a stored procedure which loops over couponusetable and sees how many rows are there for one coupon and fill that number in NoofUsesfield in coupon.
现在表中有一列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
这给了我优惠券名称及其计数 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
您提出的任何问题都将不胜感激，谢谢
回答by Gowdhaman008
Try the following snippet. You can call the the below stored procedure from your application, so that NoOfUsesin 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!
希望这可以帮助！
回答by marc_s
What's wrong with just simply using a single, simple UPDATEstatement??
仅仅使用一个简单的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 语句。
回答by bharat
You can create a trigger which updates NoofUsescolumn in Coupontable whenever
couponidis used in CouponUsetable
您可以创建一个触发器，每当NoofUses在Coupon表couponid中使用时更新表
中的CouponUse列
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
