Return string with dynamic SQL

Question

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".

Sorry I have @Error_Return as a Varchar(500) already declared but did not state that. — Michael Rowley
– Michael Rowley, Commented Dec 3, 2012 at 19:47

Ann L. · Accepted Answer · 2012-12-03 20:06:58Z

4

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

answered Dec 3, 2012 at 20:06

Ann L.

14k5 gold badges39 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joel Coehoorn · Accepted Answer · 2012-12-03 20:12:41Z

1

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.

answered Dec 3, 2012 at 20:12

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Collectives™ on Stack Overflow

Return string with dynamic SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related