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

Question

I have two tables in my database   Coupon Table    id  int  Name  nvarchar max   NoofUses  int    CouponUse Table   id int  Couponid int  CreateDate datetime    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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql