Hi Martin,

Dynamic SQL runs in its own context not in the context of SP that executed it. Because of that SP variables are not within scope of Dynamic SQL. The common practice is to store a return value to a temporary table and than use it in the calling SP.

Create Procedure sp_GetData
(
@tcWhere VarChar(100),
@tcReturn VarChar(1000) Output
)
As
Declare @lcSql VarChar(1000)
CREATE TABLE #returnvalue ( cReturn VarChar(1000) )
Set @lcSql = 'Select cField From Table where ' + @tcWhere
INSERT INTO #returnvalue Exec (@lcSql)
SET @tcReturn = (SELECT  cReturn FROM #returnvalue )

>Hello,
>
>I have a problem with a stored procedure. In this SP I build a SQL select in a variable like this:
>
>Create Procedure sp_GetData
>(
>@tcWhere VarChar(100),
>@tcReturn VarChar(1000) Output
>)
>As
>Declare @lcSql VarChar(1000)
>Set @lcSql = 'Select @tcReturn = cField From Table where ' + @tcWhere
>Exec (@lcSql)
>GO
>
>However I get the message that I have to declare @tcReturn. This means that Exec runs in a seperate space where @tcReturn is not known. Is there a way to dynamicaly build the SQL statement and get the result in a variable?
>
>TIA,
>
>Martin