sql cursor

[xxtoni001]

I am trying to write a cursor to update warranty date on products. i have 2 tables tblwarranty (with fields product code, warranty) and tmpsales wit fields (id, product, sdate,serialno, expdate, warrantyupd)

I have the following code which gives me an output on screen but does not update my tmpsales table.
-----------
CREATE PROCEDURE TESTWARRANTY AS


DECLARE @Serialno nvarchar(50)
DECLARE @Sdate Datetime
DECLARE @Expdate nvarchar(50)
DECLARE @Warranty numeric

DECLARE C1test CURSOR FOR
SELECT dbo.tmpSales.SerialNo, dbo.tmpSales.Sdate, dbo.tblWarranty.Warranty
FROM dbo.tmpSales INNER JOIN
dbo.tblWarranty ON dbo.tmpSales.Product = dbo.tblWarranty.Productcode
WHERE (dbo.tmpSales.Warrantyupd = 0)
OPEN C1test

FETCH NEXT FROM C1test
INTO @Serialno, @Sdate, @Warranty

WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE TMPSALES

SET @Expdate = DATEADD(Month, @warranty, @sdate)
, warrantyupd = '1'


FETCH NEXT FROM C1test
INTO @Serialno, @Sdate, @Warranty

END

CLOSE C1test
DEALLOCATE C1test
GO
--------------------------------------

please help me out. I am half way there and need a little boost. Am new to SQL

 

[vongrunt]

You updated variable in memory (@Expdate), not column in a table. Modify UPDATE within loop into:

UPDATE TMPSALES
    SET Expdate = DATEADD(Month, @warranty, @sdate)
    ,    warrantyupd = '1'
    WHERE SerialNo = @Serialno

Of course cursors suck, but that's for lesson No. 301

 

[HRoarke]

You're updating the proc variable @ExpDate - update the relevant column in the table, e.g.

BEGIN
    UPDATE TMPSALES
    SET [column_to_update] = DATEADD(Month, @warranty, @sdate)
        ,warrantyupd = '1'
            
    FETCH NEXT FROM C1test
    INTO @Serialno, @Sdate, @Warranty
        
END

Of course, it is always better to use a SET operation, e.g.

    UPDATE TMPSALES
    SET [column_to_update] = DATEADD(Month, @warranty, @sdate)
        ,warrantyupd = '1'
FROM    dbo.tmpSales INNER JOIN
        dbo.tblWarranty 
ON      dbo.tmpSales.Product = 
            dbo.tblWarranty.Productcode
WHERE   dbo.tmpSales.Warrantyupd = 0

Hope this helps.

"I swear by my life and my love of it that I will never live for the sake of another man, nor ask another man to live for mine."
— John Galt
Atlas Shrugged

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[SQLSister]

YOu are new, so the first thing I will tell you is that you should never use a cursor to do this.

Try the following code wihout the cursor (I am assuming that you really intended to update a field in TMPsales caleed Expdate):

UPDATE TMPSALES
    SET Expdate = DATEADD(Month, w.warranty, t.sdate)
    ,   warrantyupd = '1'
	FROM dbo.Tmpsales t  
	INNER JOIN    dbo.tblWarranty w 
	ON t.Product = w.Productcode
	WHERE t.Warrantyupd = 0

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief