Dynamic Variable Assignment - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic Variable Assignment

Author

Topic

Bert
Starting Member

1 Post

Posted - 2002-07-26 : 12:13:57

I would like to dynamically assign variable using a loop counter in a while loop, here is a snipet of the code;

declare @SerialStringLength as Int
declare @SerialNumberCounter as Int
Declare @TotalSerialNumbers as Int
Declare @SerialString as varchar(2000)
Declare @Start as Int
declare @Serial1 as char(61)
Declare @Serial2 as char(61)
Declare @Serial3 as Char(61)
Declare @Temp as varchar(2000)

/* Initialize Variables */
select @Start = 0
select @Serial1 = ''
select @serial2 = ''
select @serial3 = ''
select @SerialNumberCounter = 0
select @SerialString = 'M10189^Bob^Ted^' /*Example String */
select @Temp = ''
select @SerialStringLength = len(@SerialString)

while ((select CHARINDEX('^',@SerialString))<>0)
BEGIN
SELECT @SerialNumberCounter = @SerialNumberCounter + 1
Select @temp = 'SELECT @serial'+ cast(@SerialNumberCounter as char(1))+ ' = substring (@SerialString,1,(select CHARINDEX(''^'',@SerialString))-1)'
Exec (@Temp)
select @Start = (select CHARINDEX('^',@SerialString)) + 1
select @SerialString = substring(@SerialString,@Start,@SerialStringLength)
select @SerialStringLength = len(@SerialString)
END /* End of Serial Number Parsing*/

I am trying to strip the serial numbers (ends with a carrot) and assign them to the local variables @serial1, @serial2, @serial3. The syntax the sql string @temp looks right, but when it is executed I get an error Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@SerialString'.

Has anyone found a good way to handle this type of an issue? I found a similar entry on Dynamic SQL and tried some of the suggestions but was unable to get it to work. Thanks for any help...it would be appreciated.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-26 : 16:32:42

Take a look here:

http://www.sqlteam.com/item.asp?ItemID=2652

I don't think you can modifyy it to work with variables though, but you can easily dump them into a table and then SELECT them from it.

S.Krishnan
Starting Member

6 Posts

Posted - 2002-07-29 : 12:42:19

Just replace the @temp with the following lines and then exec (@temp)

select @temp = 'declare @serial1 as char(61), @serial2 as char(61), @serial3 as Char(61), @serialstring as varchar(2000)'
select @temp = @temp + "select @serialstring = '" + @serialstring + "' "
select @temp = @temp + 'sELECT @serial'+ cast(@serialNumberCounter as char(1))+ ' = substring (@serialstring,1,(select CHARINDEX(''^'',@serialstring))-1)'

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-29 : 12:51:09

quote:

Just replace the @temp with the following lines and then exec (@temp)

select @temp = 'declare @serial1 as char(61), @serial2 as char(61), @serial3 as Char(61), @serialstring as varchar(2000)'
select @temp = @temp + "select @serialstring = '" + @serialstring + "' "
select @temp = @temp + 'sELECT @serial'+ cast(@serialNumberCounter as char(1))+ ' = substring (@serialstring,1,(select CHARINDEX(''^'',@serialstring))-1)'

This won't work. The variables declared will be out of scope of the calling batch. In order to parameterize this dynamic sql, you will need to use the sp_executesql stored procedure.

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources