Tuesday, March 27, 2012

cursor problem

Actually My proble is

DECLARE @.SNO INT
DECLARE CURS_FOR_SNO CURSOR FOR SELECT * FROM MACB where cntrl_no='DC000429' and isnull(listed,'')='U' AND ISNULL(SNO,'')='' ORDER BY SNO
OPEN CURS_FOR_SNO
FETCH NEXT FROM CURS_FOR_SNO
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.I=@.I + 1
SET @.SNO=@.X + @.I
UPDATE MACB SET SNO=@.SNO where CURRENT OF CURS_FOR_SNO
FETCH NEXT FROM CURS_FOR_SNO
END
CLOSE CURS_FOR_SNO
DEALLOCATE CURS_FOR_SNO

it gives read only column but i want to update that so plz, solve my problem

YOu will have to pout the UPDATE syntax after the Select statement, for more information and samples, look in the BOL.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||plz give any example qry for update syntax|||

Hi,

you have defined a "Order by" which declares the cursor as readonly. Leave the Orderby and put a FOR UPDATE after your cursor declaration:

DECLARE CURS_FOR_SNO CURSOR FOR SELECT * FROM MACB where cntrl_no='DC000429' and isnull(listed,'')='U' AND ISNULL(SNO,'')='' FOR UPDATE

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||You don't need to a cursor to perform these DML operations. You can do this with a single DML statement that uses a correlated sub-query for example to count the rows and assign the SNO value.|||Sure thats one thing I forgot to mention, set based statements are in most cases faster than cursor based ones. Cursors are only not avoidable in cases you can′t use a setbased command like executing a stored procedure for n-rows (like sending mails)

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de

No comments:

Post a Comment