Newbie "Must declare variable" question

Question

Newbie "Must declare variable" question

Ryan-209402

Hall of Fame

Points: 3707
More actions
May 1, 2006 at 9:50 am

#168758

I am attempting to use a cursor in this script but I am getting errors. See script and errors below:
USE master
GO
--populate table with all user db names
TRUNCATE TABLE master.dbo.DBName
Insert master.dbo.DBName
SELECT name FROM sysdatabases WHERE name = 'pubs'
--create "array" of db names
DECLARE @dbname varchar(30)
DECLARE for_each_loop CURSOR
FOR SELECT * from master.dbo.DBName
OPEN for_each_loop
--loop through list and perform various tasks
FETCH NEXT FROM for_each_loop INTO @dbname
WHILE (@@FETCH_STATUS = 0)
BEGIN
--Rename .bak files
--EXEC master.dbo.xp_cmdshell 'dir "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\test"'
PRINT @dbname
GO
FETCH NEXT FROM for_each_loop INTO @dbname
--Destroy cursor, tables
CLOSE for_each_loop
DEALLOCATE for_each_loop
*********ERRORS**************
Server: Msg 170, Level 15, State 1, Line 21
Line 21: Incorrect syntax near '@dbname'.
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@dbname'.
What am I missing? I have tried moving the "DECLARE @dbname varchar(30)" inside and outside the cursor but still receive the error. Please help. Thanks!

Viewing 9 posts - 1 through 8 (of 8 total)

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

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

The 'GO' after the PRINT may be your problem. I'd bet the server is executing everything above it as one batch and everything below it as another (that's the purpose of 'GO'), so the variable isn't defined in the second batch.

Why not make a stored proc out of this and just call the proc?

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

Ryan-209402 Hall of Fame Points: 3707 More actions · Answer 2

Thanks for your response. I followed your suggestions and now it appears that there is a problem with deallocating the cursor. I also tried "DEALLOCATE CURSOR for_each_loop".

CREATE PROCEDURE proc

AS

DECLARE @dbname varchar(30)

DECLARE for_each_loop CURSOR

FOR SELECT * from master.dbo.DBName

OPEN for_each_loop

--loop through list and perform various tasks

FETCH NEXT FROM for_each_loop INTO @dbname

WHILE (@@FETCH_STATUS = 0)

BEGIN

PRINT @dbname

FETCH NEXT FROM for_each_loop INTO @dbname

--Destroy cursor, tables

CLOSE for_each_loop

DEALLOCATE for_each_loop

GO

Ryan-209402 Hall of Fame Points: 3707 More actions · Answer 3

Got it, I was missing "END" to end the cursor. Thanks again.

Ryan-209402 Hall of Fame Points: 3707 More actions · Answer 4

Here is another similar problem that will fit in with this thread. When I run this script:

DECLARE @table varchar(50)

SET @table = 'pubs..sysobjects'

PRINT @table

SELECT name FROM @table WHERE type='P' AND name NOT LIKE 'dt_%' AND category='0' OR type='FN' AND category='0'

I get the error:

Server: Msg 137, Level 15, State 2, Line 5

Must declare the variable '@table'.

This is puzzling because I have obviously declared the variable. ???

David McFarland SSChampion Points: 11815 More actions · Answer 5

You can't use a variable directly in place of a table name in that SELECT statement. You can accomplish it by turning the select statement into a string, and performing an EXEC() of that string. Untested, but like this:

DECLARE @strSQL varchar(4000)

DECLARE @table = 'pubs..sysobjects'

SET @strSQL = 'SELECT name FROM ' + @table + 'WHERE type=''P'' AND name NOT LIKE ''dt_%'' AND category=''0'' OR type=''FN'' AND category=''0'''

EXEC (@strSQL)

Those single quotes that I changed are still single quotes, just multiple ones. This is how you get single quotes to show up inside of a string that is surrounded by single quotes. Also note that the parentheses around the variable are required for Dynamic SQL.

David McFarland SSChampion Points: 11815 More actions · Answer 6

I already noticed a minor bug. I joined the DECLARE AND SET for the @table variable. Just replace the line that says

DECLARE @table = 'pubs..sysobjects'

with

DECLARE @table varchar(50)

SET @table = 'pubs..sysobjects'

and it should work, unless I have another syntax error.

David McFarland SSChampion Points: 11815 More actions · Answer 7

As an aside, I actually looked at what your cursor is doing. Have you considered using the sadly undocumented sp_MSforeachtable stored procedure to see if it fits your needs?

It's not in BOL, but is fairly well documented on the web. I don't know a lot of dbas who don't keep it in their virtual toolbox.

Ryan-209402 Hall of Fame Points: 3707 More actions · Answer 8

This did the trick. Thanks to everyone for your help!