SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


please help with cursor syntax


please help with cursor syntax

Author
Message
KoldCoffee
KoldCoffee
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1537 Visits: 1905
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?
David Webb-CDS
David Webb-CDS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1580 Visits: 8586
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
KoldCoffee
KoldCoffee
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1537 Visits: 1905
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?
David Webb-CDS
David Webb-CDS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1580 Visits: 8586
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
KoldCoffee
KoldCoffee
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1537 Visits: 1905
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

J Livingston SQL
J Livingston SQL
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5483 Visits: 35451
just a thought


;with SD as (
SELECT name,
database_id,
create_date
FROM sys.databases
where name not in ('master','tempdb', 'model', 'msdb')
)

SELECT SD.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

KoldCoffee
KoldCoffee
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1537 Visits: 1905
Yes, I agree. There's also an sp_msforeachdb function available....

But I needed to learn the cursor
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39690 Visits: 38562
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
KoldCoffee
KoldCoffee
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1537 Visits: 1905
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86906 Visits: 41105
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search