please help with cursor syntax

  • I have a simple cursor and I need help fixing syntax.(I know that in this scenario I am using cursor properly).

    The purpose of the cursor (copied below) is just to output the names of all databases on my server. Later I will use dynamic sql (not copied below) to display the respective metrics (user_scans, user_lookups) for each database from sys.dm_db_index_usage_stats.

    For the cursor part I'm getting the following error:

    Msg 16915, Level 16, State 1, Line 4

    A cursor with the name 'getDatabaseName' already exists.

    Msg 137, Level 15, State 2, Line 3

    Must declare the scalar variable "@DatabaseName".

    Msg 137, Level 15, State 2, Line 6

    Must declare the scalar variable "@DatabaseName".

    Msg 137, Level 15, State 2, Line 8

    Must declare the scalar variable "@DatabaseName".

    my cursor script

    use MiguelsApp

    DECLARE @DatabaseName varchar(max)

    DECLARE getDatabaseName CURSOR FOR

    SELECT name, database_id, create_date

    FROM sys.databases

    where name not in ('master', 'tempdb', 'model', 'msdb');

    GO

    OPEN getDatabaseName

    FETCH NEXT

    FROM getDatabaseName INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    print @DatabaseName

    FETCH NEXT FROM getDatabaseName

    INTO @DatabaseName

    END

    close getDatabaseName

    deallocate getDatabaseName

    Will someone please tell me where my syntax is wrong so it will run?

  • Remove the 'GO'. That breaks the script up into 2 separately executable pieces and you need this to be one execution stream.

    Also, is there a specific reason to use a cursor? Most tasks can be accomplished using set-oriented sql with great gains in efficiency.


    And then again, I might be wrong ...
    David Webb

  • thanks for help. Yes, cursor will be find for this case. I removed 'GO' and now I get the following error.

    Msg 16915, Level 16, State 1, Line 4

    A cursor with the name 'getDatabaseName' already exists.

    Msg 16924, Level 16, State 1, Line 8

    Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

    running this

    use MiguelsApp

    DECLARE @DatabaseName varchar(max)

    DECLARE getDatabaseName CURSOR FOR

    SELECT name, database_id, create_date

    FROM sys.databases

    where name not in ('master', 'tempdb', 'model', 'msdb');

    OPEN getDatabaseName

    FETCH NEXT

    FROM getDatabaseName INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    print @DatabaseName

    FETCH NEXT FROM getDatabaseName

    INTO @DatabaseName

    END

    close getDatabaseName

    deallocate getDatabaseName

    do you see anything else wrong?

  • The cursor has been defined in one of the previous executions and never got closed and deallocated. Issue the close and deallocate commands by themselves or just get a new connection.

    The number of columns in your select list in the cursor has to match the number of columns you are fetching. You are missing 2 columns in the fetch.


    And then again, I might be wrong ...
    David Webb

  • You were very helpful to me. I removed the two extra columns I don't need and ran cursor in a new window and now I have results. Thanks you.

    what worked

    use MiguelsApp

    DECLARE @DatabaseName varchar(max)

    DECLARE getDatabaseName CURSOR FOR

    SELECT name

    FROM sys.databases

    where name not in ('master', 'tempdb', 'model', 'msdb');

    OPEN getDatabaseName

    FETCH NEXT

    FROM getDatabaseName INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    print @DatabaseName

    FETCH NEXT FROM getDatabaseName

    INTO @DatabaseName

    END

    close getDatabaseName

    deallocate getDatabaseName

  • just a thought

    ;with SD as (

    SELECTname,

    database_id,

    create_date

    FROM sys.databases

    where name not in ('master','tempdb', 'model', 'msdb')

    )

    SELECTSD.name,

    SD.create_date,

    IUS.database_id,

    IUS.index_id,

    IUS.user_seeks,

    IUS.user_scans,

    IUS.user_lookups,

    IUS.user_updates,

    IUS.last_user_seek

    FROM SD

    LEFT OUTER JOIN sys.dm_db_index_usage_stats AS IUS

    ON SD.database_id= IUS.database_id

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Yes, I agree. There's also an sp_msforeachdb function available....

    But I needed to learn the cursor

  • KoldCoffee (4/1/2013)


    Yes, I agree. There's also an sp_msforeachdb function available....

    But I needed to learn the cursor

    Actually, we can probably show you how to do this without using a cursor if you show us what you are trying to accomplish.

  • Yes, except that David Webb-200187, good man that he is, answered the question I had, which is wonderful. Sometimes you just need a relevant answer, yes?

    Thank you David Webb-200187, for having soul.

  • KoldCoffee (4/2/2013)


    Yes, except that David Webb-200187, good man that he is, answered the question I had, which is wonderful. Sometimes you just need a relevant answer, yes?

    Thank you David Webb-200187, for having soul.

    Considering that cursors are usually a bad thing and the others were trying to help you avoid them, I think the others have a soul, as well. 😉

    Sometimes you just need a different answer than what you asked for so you can do it right. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • LP has soul? If you say so :pinch:.

  • KoldCoffee (4/14/2013)


    LP has soul? If you say so :pinch:.

    I don't know if I should take this as a joke or an insult.

  • truth is that I would be honored to meet you all at a SQL Pass summit one day. Each of you have been tremendously helpful to my SQL learning.

    Thank you Lynn. Lowell, i'm still thinking on Hierarchies too and your articles are among my pile of reads. Good night!

  • Well, I won't be at Pass this year, but I will sure try to make it to PASS in 2014.

  • Well, I hope to see you there!

Viewing 15 posts - 1 through 14 (of 14 total)

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