cursor problem

Hot Topic

SQL Server 2005 Express: Weird error message running SELECT COUNT query
Unable to read local eventlog (the parameter is incorrect)
can we automate the data mining models management?
SQL Mail
Does Full-text extract searchable metadata (Properties) from documents?
FOREACH Loop Container: Continue ?
convert minutes to hours
Identity column not returnd after AddNew / Update with ADO and SQL 2005
Dynamically define the height of a chart
How can I skip an initialize snapshot?

SQL Server

about processXMLdata sample,how to insert a return in end of each row data of the result text file?
Select multiple values from a dropdown in RS 2000?
SSIS Connection Object and Expressions
Configuration of Database Mail
Could anyone kindly tell me,What tool can I use to Create db or restore db or backup db in SQL 2005 Express?
Memory Issue with Simple CLR Based Table-Value-Function (TVF)
problem inserting datetime
AS2005 September CTP Memory Error
Workflow using SSIS?
Problem with instalation

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




Answer this question

cursor problem

  • MessDev

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

  • ashish tiwari

    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.

  • Jimmy Xiong

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

  • Bill Barnett - MS

    plz give any example qry for update syntax

  • DRAYKKO

    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
    ---
  • cursor problem
Answer this question