Post reply

Cursors

  • April 30, 2011 at 1:08 am

    #238699

    [font="Tahoma"]

    Hello Friends,

    I am using cursors for the first time and i have written a code to drop tables using cursors. following is the code.

    DECLARE @TBLNAME VARCHAR(50)

    DECLARE TBL_CURSOR CURSOR FOR

    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'DEPARTMENTBCP%'

    OPEN TBL_CURSOR

    FETCH NEXT FROM TBL_CURSOR INTO @TBLNAME

    WHILE @@FETCH_STATUS = 0

    BEGIN

    DROP TABLE @TBLNAME

    --PRINT @TBLNAME

    FETCH NEXT FROM TBL_CURSOR INTO @TBLNAME

    END

    CLOSE TBL_CURSOR

    DEALLOCATE TBL_CURSOR

    However, i am getting the below error message.

    Incorrect syntax near '@TBLNAME'.

    Please let me know what exactly is the issue with the script. can't we use variables like this.

    [/font]

  • April 30, 2011 at 2:06 am

    #1318044

    You can't use a DROP TABLE statement using a variable unless you're using dynamic sql.

    Are you familiar with dynamic SQL? What skill level do you have?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • April 30, 2011 at 4:24 am

    #1318052

    [font="Tahoma"]

    Hello Lutz,

    Thanks for your response. Yes, i was able to drop the tables using dynamic query. But i just wanted to know if it was possible in the way that i had mentioned.

    [/font]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply