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
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