Cursor not working

Question

Post reply

Cursor not working

Francky88

Old Hand

Points: 359
More actions
May 2, 2008 at 10:10 am

#91820

Hello,
I am trying ti build a cursor that parses all the databases on my sql instance:
[highlight=#ffff11]declare @database nvarchar(128)
declare mycursor CURSOR LOCAL
FOR select name from sysdatabases where dbid>5
OPEN mycursor
fetch next from mycursor into @database
WHILE @@FETCH_STATUS=0
BEGIN
select @database
select * from @database.dbo.sysobjects
FETCH next from mycursor into @database
END
CLOSE mycursor
DEALLOCATE mycursor[/highlight]
I have no problem to print the @database but when i want to use the variable in the
select * from @database.dbo.sysobjects
Here is what sql returns to me:
Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near '.'.
Can somebody help??
Thanks a lot
Franck

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

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

You cannot use a variable as part of an object name.

If you post what you are specifically trying to do, we can probably give you some pre-tested code.

You should also look at the system stored procedure sp_MSForEachDB

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

Heh... yeech... you can use a cursor, if you like... here's your's with the fix...

DECLARE @MyCmd VARCHAR(100)

declare @database nvarchar(128)

declare mycursor CURSOR LOCAL

FOR select name from master.dbo.sysdatabases where dbid>5

OPEN mycursor

fetch next from mycursor into @database

WHILE @@FETCH_STATUS=0

BEGIN

select @database

SET @MyCmd = 'select * from '+@database+'.dbo.sysobjects'

EXEC (@MyCmd)

FETCH next from mycursor into @database

END

CLOSE mycursor

DEALLOCATE mycursor

... and you can use the sp_MSForEachDB... but that's a cursor too...

... or, we can use a set-based solution... 😉

--===== Declare a variable to hold the command we're going to build

DECLARE @MyCmd NVARCHAR(MAX)

--===== Build the command to interrogate every database as if we were using a cursor.

SELECT @MyCmd = COALESCE(@MyCmd+CHAR(10),'')

+ 'SELECT '''+Name+''' AS DBName,* FROM '+Name+'.dbo.SysObjects'

FROM Master.Sys.DataBases

WHERE DataBase_ID > 5

--===== Display, then execute the cursor

PRINT @MyCmd

EXEC (@MyCmd)

... and, if you want it all as a single result set, we can do that, too...

--===== Declare a variable to hold the command we're going to build

DECLARE @MyCmd NVARCHAR(MAX)

--===== Build the command to interrogate every database as if we were using a cursor.

-- If you want to include report servers, we'll need to do those separately

-- because of collation problems with some of the names. You'd have this same

-- problem if you used a cursor to insert into one table.

SELECT @MyCmd = COALESCE(@MyCmd+' UNION ALL'+CHAR(10),'')

+ 'SELECT '''+Name+''' AS DBName,* FROM '+Name+'.dbo.SysObjects'

FROM Master.Sys.DataBases

WHERE DataBase_ID > 5

AND Name NOT LIKE 'AdventureWorks%'

AND Name NOT LIKE 'ReportServer%'

--===== Display, then execute the cursor

PRINT @MyCmd

EXEC (@MyCmd)

Just say "NO" to cursors! 😛

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

Francky88 Old Hand Points: 359 More actions · Answer 3

Hi Jeff,

Thank you for you answer. Helps a lot.

What's the problem with cursors?

Franck

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 4

Cursors = loathesome

Lots of posts in both SQL Server Central and the MSDN:

http://www.sqlservercentral.com/Forums/Topic480367-338-1.aspx

http://www.sqlservercentral.com/Forums/Topic488556-8-1.aspx

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1372104&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=892822&SiteID=1

Louis Davidson

Kent Waldrop

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=447559&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=437891&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=588762&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=458950&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=368222&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=625956&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=252376&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1387094&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=530067&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1064381&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=170657&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=480333&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=239211&SiteID=1

Umachandar Jayachandran

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=485117&SiteID=1

Umachandar Jayachandran

Jens Suessmeyer

and tons more.

Cursors put you into a "record based process" whereas Transact SQL is a language that works best with "set based processes". There are a few situations in which a cursor MIGHT be the best solution; however, these are rather few. In general, it is best if you look for a set-based solution before thinking about looking into a cursor-based solution.

Francky88 Old Hand Points: 359 More actions · Answer 5

Thanks.

I have to learn on this set thing. So far, i don't really see how it works...

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 6

I guess I need to also confess that I will at times over-react to cursor based code so take it a little with a grain of salt. Also, please don't misunderstand. I meant nothing personal.

Kent

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

Levoin (5/5/2008)
Hi Jeff,
Thank you for you answer. Helps a lot.
What's the problem with cursors?
Franck

Two things... first, they consume more resources and locks than necessary... those can be mostly overcome by using a "firehose" cursor which is a Read Only/Fast Forward cursor.

The second thing is that they use a WHILE loop. In other words, they process one row at a time and that's not the nature of databases. Because they process only one row at a time, they override the basic nature of how databases want to be processed and that makes them terribly slow compared to set-based solutions. There are, of course, exceptions where a While loop can be faster than a set based solution, but they're very very rare (Proper case function is one). But, for the most part, (99.99% in my opinion), there's no need for the performance problems that are inherent in cursors and While loops.

Great example of this is the running total problem... yep, you can use a cursor/while loop to do it. Write one that works against a million rows and then take a look at the set based solution in the following article to compare performance...

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

Also keep in mind that just because something doesn't have an explicit loop defined, doesn't mean it's a set based solution. That's covered in the following article as well as the one above...

http://www.sqlservercentral.com/articles/T-SQL/61539/

Lot's of folks say that they need a cursor/while loop to step through databases... yeah, you can and that might not be a bad way to go... but in SQL Server 2005 and because of its VARCHAR(MAX), there's really no longer the need there, either (simple example follows)...

--===== Declare a variable to hold the command we're going to build

DECLARE @MyCmd NVARCHAR(MAX)

--===== Build the command to interrogate every database as if we were using a cursor.

-- If you want to include report servers, we'll need to do those separately

-- because of collation problems with some of the names. You'd have this same

-- problem if you used a cursor to insert into one table.

SELECT @MyCmd = COALESCE(@MyCmd+' UNION ALL'+CHAR(10),'')

+ 'SELECT '''+Name+''' AS DBName,* FROM '+Name+'.dbo.SysObjects'

FROM Master.Sys.DataBases

WHERE DataBase_ID > 5

AND Name NOT LIKE 'ReportServer%'

--===== Display, then execute the cursor

PRINT @MyCmd

EXEC (@MyCmd)

Instead of getting multiple results sets (1 for each DB) like what a cursor might return or having to populate a table with each iteration of a cursor, it all comes back as a single set and it does it with pretty good speed, not to mention how simple the code becomes. It works kinda like sp_MSForEachDB but without the cursor in the background.

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

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 8

Hmm 99.99%. Maybe as far as cursors go you "can't loathe them enough."

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

Kent Waldrop (5/5/2008)
Hmm 99.99%. Maybe as far as cursors go you "can't loathe them enough."

Heh... to be clear, I've never written a cursor except to show how slow they are... :hehe:

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

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 10

I was working with Sybase before they implemented cursors. Cursors came out and they were the new buzzword. Managers wanted cursors, cursors and more cursors -- and THEY GOT THEM! I was ready to die. I remember going to a couple of different job interviews and being asked if I knew how to use cursors. (Gnawing my toungue off at that point.) Nothing has come close to causing me the amount of rework over the years that cursors have. My favorite topic for ranting, really.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

Now, you know why I've chosen the Avatar that I have... 🙂

Cursors = RBAR

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

Michael Meierruth SSChampion Points: 10051 More actions · Answer 12

Heh... to be clear, I've never written a cursor except to show how slow they are... :hehe:

Given that Jeff is in luv with cursors, we all really need to be looking seriously for a problem that can only be solved with a cursor.:w00t:

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 13

I don't think you need to worry about helping Jeff be well rounded; look at the Avatar -- a "circle" is a primary feature! 🙂

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 14

Michael Meierruth (5/7/2008)
Heh... to be clear, I've never written a cursor except to show how slow they are... :hehe:
Given that Jeff is in luv with cursors, we all really need to be looking seriously for a problem that can only be solved with a cursor.:w00t:

Heh... no... don't need many more challenges this week... 😛

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