Beginning to hate dynamic sql and variables

Question

Post reply

Beginning to hate dynamic sql and variables

Simon_L

SSCarpal Tunnel

Points: 4669
More actions
September 10, 2008 at 4:08 am

#71085

The following code keeps telling me
Must declare the variable '@TableName'.
why oh why oh why me ... the only glimmer of hope is cern might cause the world to end and I wont have to think about it
~si
_{declare @string varchar(2000)}
declare @return char (2)
SET @return = CHAR (13) + CHAR (10)
set @string =
'DECLARE @IDTableName TABLE (TableName VARCHAR(128),DeleteOrder INT IDENTITY)'+@return
+'DECLARE @Stmt VARCHAR (512),'+@return
+'@Counter INT,'+@return
+'@TableName SYSNAME' +@return
+'INSERT @IDTableName([TableName])' +@return
+'SELECT TBL_NAME FROM Meta_Table WHERE ID_BASED = 1 ORDER BY COPYRANK ASC' +@return
+'SELECT @Counter = MAX (DeleteOrder) FROM @IDTableName' +@return
+'WHILE @Counter > 0' +@return
+'BEGIN' +@return
+'SELECT @TableName = [TableName] FROM @IDTableName WHERE [DeleteOrder] = @Counter' +@return
+'SET @Stmt ='+char(39)+'DELETE @TableName WHERE IID = 0'+char(39)+@return
+'EXEC (@Stmt)' +@return
+'SET @Counter = @Counter - 1' +@return
+'END'
exec (@string)

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi Simon

@TableName is out of scope of the statement which is supposed to be using it. You need to change your line to

+'SET @Stmt ='+char(39)+'DELETE '' + @TableName + '' WHERE IID = 0'+char(39)+@return

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 2

Thanks Chris .. deeply appreciated 🙂

~si

one day I'll wrap my head round the whole dynamic sql /variable scope thing

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

You're welcome Simon, thanks for the feedback. Have another coffee, it helps 🙂

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden