Click here to Skip to main content
15,065,328 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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?

Please help me.

how can i do?

Regards,
Narasiman P.
Posted
Comments
Sandeep Mewara 27-Apr-13 9:47am
   
Tried this: https://www.google.co.in/search?q=How+to+write+a+cursor+inside+a+stored+procedure&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US:official&client=firefox-a&channel=rcs

Hello,
I don't think that you need to use a cursor. The cursors are too slow. You should never use cursors.

Here is an UPDATE command which resolves your problem:

SQL
UPDATE [Coupon table] SET NoofUses = Total.Nr
FROM [Coupon table] INNER JOIN 
         (SELECT COUNT(Couponid) AS Nr, Couponid FROM [CouponUse Table] GROUP BY Couponid)Total
ON Total.Couponid = [Coupon table].id
   
v2
As Sandeep sugested. There are many examples on the web for this. however, with cursors they can be slow. There are many uses for them but the majority of instances you can use normal looks, set based queries or use RANK / DENSE_RANK functions for ordering so that you can insert / update based on query.

Try to see if you can accomplish this without a cursor. If a cursor is needed then use this format. Also note the extra switches for the cursor (FAST_FORWARD, READONLY etc)

SQL Cursors:

http://msdn.microsoft.com/en-GB/library/ms180169.aspx[^]

SQL
DECLARE @Col1 INT,
        @Col2 VARCHAR(100)

DECLARE cur CURSOR FAST_FORWARD FOR 
SELECT col1, col2
FROM TableName

OPEN cur

FETCH NEXT FROM cur 
INTO @Col1, @Col2

WHILE @@FETCH_STATUS = 0
BEGIN
    --Write your insert / update statement here based on @col variables.


    FETCH NEXT FROM cur
    INTO @Col1, @Col2
END 
CLOSE cur;
DEALLOCATE cur;


Let me know if there is anything else?
   
C#
class sss
{



}
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900