Posted by Erland Sommarskog on 09/28/07 21:47

bobc (bcanavan@fmbnewhomes.com) writes:
> 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.

Wait a minute. You are in a relational database now, not in a C++ program.

I didn't ask why you returned a delimited string in the procedure in
your first post, but if you intend on unpack the string in the
calling procedure, you are on the wrong track altogther. Pass the
data in a table, and perform your operations on the whole set.

> The error I get when calling from vb.net is:
> Must declare the variable '@tempVal'.
>
> Two manual traces indicate the logic is ok.

No, it's not. A batch of dynamic SQL is a scope of its own, and you
cannot access variables in outer scope. If you want to assign
variables @d1 to @d5, that's five SELECT statements.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]