The reason you are getting the DECLARE
error from your dynamic statement is because dynamic statements are handled in separate batches, which boils down to a matter of scope. While there may be a more formal definition of the scopes available in SQL Server, I’ve found it sufficient to generally keep the following three in mind, ordered from highest availability to lowest availability:
Global:
Objects that are available server-wide, such as temporary tables created with a double hash/pound sign ( ##GLOBALTABLE
, however you like to call # ). Be very wary of global objects, just as you would with any application, SQL Server or otherwise; these types of things are generally best avoided altogether. What I’m essentially saying is to keep this scope in mind specifically as a reminder to stay out of it.
IF ( OBJECT_ID( 'tempdb.dbo.##GlobalTable' ) IS NULL ) BEGIN CREATE TABLE ##GlobalTable ( Val BIT ); INSERT INTO ##GlobalTable ( Val ) VALUES ( 1 ); END; GO -- This table may now be accessed by any connection in any database, -- assuming the caller has sufficient privileges to do so, of course.
Session:
Objects which are reference locked to a specific spid. Off the top of my head, the only type of session object I can think of is a normal temporary table, defined like #Table. Being in session scope essentially means that after the batch ( terminated by GO
) completes, references to this object will continue to resolve successfully. These are technically accessible by other sessions, but it would be somewhat of a feat do to so programmatically as they get sort of randomized names in tempdb and accessing them is a bit of a pain in the ass anyway.
-- Start of session; -- Start of batch; IF ( OBJECT_ID( 'tempdb.dbo.#t_Test' ) IS NULL ) BEGIN CREATE TABLE #t_Test ( Val BIT ); INSERT INTO #t_Test ( Val ) VALUES ( 1 ); END; GO -- End of batch; -- Start of batch; SELECT * FROM #t_Test; GO -- End of batch;
Opening a new session ( a connection with a separate spid ), the second batch above would fail, as that session would be unable to resolve the #t_Test
object name.
Batch:
Normal variables, such as your @value1
and @value2
, are scoped only for the batch in which they are declared. Unlike #Temp
tables, as soon as your query block hits a GO
, those variables stop being available to the session. This is the scope level which is generating your error.
-- Start of session; -- Start of batch; DECLARE @test BIT = 1; PRINT @test; GO -- End of batch; -- Start of batch; PRINT @Test; -- Msg 137, Level 15, State 2, Line 2 -- Must declare the scalar variable "@Test". GO -- End of batch;
Okay, so what?
What is happening here with your dynamic statement is that the EXECUTE()
command effectively evaluates as a separate batch, without breaking the batch you executed it from. EXECUTE()
is good and all, but since the introduction of sp_executesql()
, I use the former only in the most simple of instances ( explicitly, when there is very little “dynamic” element of my statements at all, primarily to “trick” otherwise unaccommodating DDL CREATE
statements to run in the middle of other batches ). @AaronBertrand’s answer above is similar and will be similar in performance to the following, leveraging the function of the optimizer when evaluating dynamic statements, but I thought it might be worthwhile to expand on the @param
, well, parameter.
IF NOT EXISTS ( SELECT 1 FROM sys.objects WHERE name = 'TblTest' AND type = 'U' ) BEGIN --DROP TABLE dbo.TblTest; CREATE TABLE dbo.TblTest ( ID INTEGER, VALUE1 VARCHAR( 1 ), VALUE2 VARCHAR( 1 ) ); INSERT INTO dbo.TblTest ( ID, VALUE1, VALUE2 ) VALUES ( 61, 'A', 'B' ); END; SET NOCOUNT ON; DECLARE @SQL NVARCHAR( MAX ), @PRM NVARCHAR( MAX ), @value1 VARCHAR( MAX ), @value2 VARCHAR( 200 ), @Table VARCHAR( 32 ), @ID INTEGER; SET @Table = 'TblTest'; SET @ID = 61; SET @PRM = ' @_ID INTEGER, @_value1 VARCHAR( MAX ) OUT, @_value2 VARCHAR( 200 ) OUT'; SET @SQL = ' SELECT @_value1 = VALUE1, @_value2 = VALUE2