CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Smartboy
Smartboy

Reputation: 1006

How to write a cursor inside a stored procedure in SQL Server 2008

I have two tables in my database

Coupon Table

CouponUse Table

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

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.

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

Upvotes: 11

Views: 120399

Answers (3)

bharat
bharat

Reputation: 11

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

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

Upvotes: 1

Gowdhaman008
Gowdhaman008

Reputation: 1323

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.

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!

Upvotes: 16

marc_s
marc_s

Reputation: 754388

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

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.

Upvotes: 13

Related Questions