How to write a cursor inside a stored procedure in SQL Server 2008
sql, sql-server, sql-server-2008, t-sql
Solution
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.
Problem
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