Posted by bobc on 09/28/07 21:36

In my stored procedure, I want to parse @ArrayOfDays into @d1 through
@d5.

@ArrayOfDays is a varchar input parameter containing,
for example, "1.7.21.25.60." - five elements.

Most active vars:
@i - loop counter
@char - current char in string
@tempVal - contains the current element as it is being built
@tempValExecString - contains SELECT stmt for EXEC()

I'm using EXEC() to execute a dynamically built SELECT.

The error I get when calling from vb.net is:
Must declare the variable '@tempVal'.

Two manual traces indicate the logic is ok.

I suspect my assignment statement for @tempValExecString.

Any help would be appreciated. - BobC

----------------------------------------------------------
DECLARE
@d1 varchar(3),
@d2 varchar(3),
@d3 varchar(3),
@d4 varchar(3),
@d5 varchar(3),
@i int,
@char char(1),
@tempVal varchar(3),
@tempValExecString varchar(30)

SELECT @tempVal = ''
SELECT @i = 1

WHILE @i < LEN(@ArrayOfDays)
BEGIN
SELECT @char = SUBSTRING(@ArrayOfDays, @i, 1)
WHILE @char <> '.'
BEGIN
SELECT @tempVal = @tempVal + @char
SELECT @char = SUBSTRING(@ArrayOfDays, @i+1, 1)
IF @char = '.'
BEGIN
/* the following should produce "SELECT @d1 = 1" when it reads the
first period(.) */
SELECT @tempValExecString = 'SELECT @d' + LTRIM(RTRIM(STR(@i))) + '
= @tempVal'
EXEC(@tempValExecString)
SELECT @tempVal = ''
SELECT @i = @i + 1
END
SELECT @i = @i + 1
END
END
----------------------------------------------------------

[Back to original message]