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