CODEWITHSUNDEEP
sqlsql-server
Michael Rowley
Michael Rowley

Reputation: 667

Return string with dynamic SQL

I am using SQL Server 2008 and I have a variable @sqlFinal which is of type Varchar(500).

I fill the variable as the stored procedure runs. I want to dynamically return what is in the string to show the results

SELECT @sqlFinal = 'SELECT @Error_Return AS Final_Report'
--PRINT @sqlFinal
EXEC (@sqlFinal)

But I get the following error

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Error_Return".

Upvotes: 1

Views: 395

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

The EXEC() function creates a new execution scope. Variables, like @Error_Return, that are defined in the the current scope will not be available in the Exec() function. Look into sp_executesql instead.

Upvotes: 1

Ann L.
Ann L.

Reputation: 13965

I am assuming that @Error_Return is in the same scope as @SqlFinal?

If you just need to return the contents of @Error_Return, you can just execute this line:

SELECT @Error_Return as Final_Report

... making it a static SQL line rather than a dynamic one.

But if that's not acceptable, you may have to use sp_executeSQL instead. This allows you to pass variables to the line you're executing. 

Declare @Error_Return VARCHAR(10)
Set @Error_return= 'Whatever'

exec sp_executesql N'SELECT @Error_Return as Final_Report', N'@Error_Return varchar(10)', @Error_Return

Upvotes: 4

Related Questions