locked
Dynamic T-SQL and spexecutesql RRS feed

  • Question

  • Morning guys,

    I have had an issue for the last couple of days trying to get sp_executesql to work with a dynamic variable. The code I have at the moment is as follows:

    SELECT @Sql = '
    				SELECT TOP 1
    					 NULL
    					,ECP.usecounts
    					,ECP.plan_handle
    					,ECP.bucketid
    					,ECP.objtype
    					,EST.[text]
    					,EPS.last_elapsed_time
    					,EPS.last_execution_time
    					,EPS.last_logical_reads
    					,EPS.last_physical_reads
    					,EPS.last_logical_writes
    					,EQS.total_worker_time
    					,@@ROWCOUNT
    				FROM sys.dm_exec_cached_plans ECP (NOLOCK)
    				CROSS APPLY
    					 sys.dm_exec_sql_text (ECP.plan_handle) EST
    				CROSS APPLY
    					 sys.dm_exec_procedure_stats EPS
    				CROSS APPLY sys.dm_exec_query_stats EQS
    				WHERE ECP.cacheobjtype = ''Compiled Plan''
    				   AND ECP.objtype =  ''Proc''
    				   AND EST.[text] NOT LIKE ''%FOR DBA USE ONLY%''
    				   AND EST.[text] LIKE' + ' ' + @ProcName
    
    
    -- Clear the Procedure Cache for any pre-existing cached plans for the Procedure being tested --
    
    BEGIN
    	EXEC 
    	('
    	INSERT INTO ' + @DeveloperSQLResults + '
    	EXEC sp_executesql ' + @SQL + '
    	')
    END
    

    @DeveloperSQLResults is a dynamically created table further up the script which works fine if I hard code the name of the table into the sp_executesql statement. The issue is trying to link this with the @SQL variable.

    The error I am getting is as follows:

    -------------------------------------------------------------------------------

    Msg 201, Level 16, State 10, Procedure sp_executesql, Line 1
    Procedure or function 'sp_executesql' expects parameter '@statement', which was not supplied.

    -------------------------------------------------------------------------------

    As I mentioned above, if I hardcode the table name so that the SQL statement looks like:

    BEGIN
    	EXEC 
    	('
    	INSERT INTO ##TableName
    	EXEC sp_executesql @SQL
    	')
    END

    ... the statement completes without any errors.

    I am at a loss having tried numerous ways of constructing the code.

    Any ideas?

    Cheers!

    • Changed type HunchbackMVP Wednesday, November 14, 2012 1:49 PM
    Wednesday, November 14, 2012 8:43 AM

Answers

  • Why do you have a variable name for the table in the first place? You could have a hard-coded name and then use sp_rename to change later.

    Also, there is no need for nested dynamic SQL. Even with the dynamic SQL, you could do:

    SELECT @sql = 'INSERT ' + quotename(@DeveloperSQLResults) +
        SELECT TOP 1
           ...
        WHERE ECP.cacheobjtype = ''Compiled Plan''
             AND ECP.objtype =  ''Proc''
          AND EST.[text] NOT LIKE ''%FOR DBA USE ONLY%''
          AND EST.[text] LIKE @ProcName'
    EXEC sp_executesql @SQL, N'@ProcName sysname', @ProcName


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Kalman Toth Tuesday, November 20, 2012 3:39 PM
    Wednesday, November 14, 2012 9:30 AM

All replies

  • Hello GhostFaceKill4h,

      Please try the below code

     

    SELECT @Sql = '
    				SELECT TOP 1
    					 NULL
    					,ECP.usecounts
    					,ECP.plan_handle
    					,ECP.bucketid
    					,ECP.objtype
    					,EST.[text]
    					,EPS.last_elapsed_time
    					,EPS.last_execution_time
    					,EPS.last_logical_reads
    					,EPS.last_physical_reads
    					,EPS.last_logical_writes
    					,EQS.total_worker_time
    					,@@ROWCOUNT
    				FROM sys.dm_exec_cached_plans ECP (NOLOCK)
    				CROSS APPLY
    					 sys.dm_exec_sql_text (ECP.plan_handle) EST
    				CROSS APPLY
    					 sys.dm_exec_procedure_stats EPS
    				CROSS APPLY sys.dm_exec_query_stats EQS
    				WHERE ECP.cacheobjtype = ''Compiled Plan''
    				   AND ECP.objtype =  ''Proc''
    				   AND EST.[text] NOT LIKE ''%FOR DBA USE ONLY%''
    				   AND EST.[text] LIKE' + ' ''' + @ProcName + ''''
    -- Clear the Procedure Cache for any pre-existing cached plans for the Procedure being tested --
    EXEC
    	(
    	'
    	INSERT INTO ' + @DeveloperSQLResults + '
    	EXEC sp_executesql ''' + REPLACE(@SQL,'''','''''') + '''
    	'
    	)
    	
    	


    Best Regards Sorna

    • Proposed as answer by Kalman Toth Tuesday, November 20, 2012 3:39 PM
    Wednesday, November 14, 2012 9:23 AM
  • Why do you have a variable name for the table in the first place? You could have a hard-coded name and then use sp_rename to change later.

    Also, there is no need for nested dynamic SQL. Even with the dynamic SQL, you could do:

    SELECT @sql = 'INSERT ' + quotename(@DeveloperSQLResults) +
        SELECT TOP 1
           ...
        WHERE ECP.cacheobjtype = ''Compiled Plan''
             AND ECP.objtype =  ''Proc''
          AND EST.[text] NOT LIKE ''%FOR DBA USE ONLY%''
          AND EST.[text] LIKE @ProcName'
    EXEC sp_executesql @SQL, N'@ProcName sysname', @ProcName


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Kalman Toth Tuesday, November 20, 2012 3:39 PM
    Wednesday, November 14, 2012 9:30 AM
  • Thanks for the replies.

    Sorna, there is a syntax error with the T-SQL you have provided.

    Erland, the procedure will be used by multiple people at the same time generating different results sets. The name has to be unique at the point of creation so I can not hard code the table names at any point.

    I will try the QUOTENAME example now and let you know.

    Thanks once again.

    Wednesday, November 14, 2012 9:38 AM
  • Erland, the second example worked perfect.

    Thanks so much for the help.

    Wednesday, November 14, 2012 9:40 AM
  • Erland, the procedure will be used by multiple people at the same time generating different results sets. The name has to be unique at the point of creation so I can not hard code the table names at any point.

    In your example you had a global temp table. Why not a local temp table?

    You could also have a permanent table which is keyed by @@spid.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, November 14, 2012 11:26 AM
  • >You could also have a permanent table which is keyed by @@spid

    spid can be recycled. A better choice is CookieID or similar.


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Wednesday, November 14, 2012 11:59 AM
    Wednesday, November 14, 2012 11:58 AM
  • spid can be recycled. A better choice is CookieID or similar.

    My assumption is that the data is only used within the connection. If data is persisted over disconnects, you need something else.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, November 14, 2012 1:43 PM
  • The reason for using global temporary tables rather than local is because the scope of the script requires objects outside of the current session to be able to view the results.

    Just to add, this data doesn't need to be stored permanently which was the reason for choosing a global over a physical table.

    Wednesday, November 14, 2012 2:19 PM
  • >The reason for using global temporary tables rather than local

    For a stable solution use a permanent table. ##globaltemptable has very limited use in database applications. On the other hand #temptable is widely used.


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Tuesday, November 20, 2012 3:42 PM
  • Hi,

    Please try

    SELECT @Sql = 'SELECT TOP 1 NULL,ECP.usecounts,ECP.plan_handle,ECP.bucketid,ECP.objtype,EST.[text] ,EPS.last_elapsed_time,EPS.last_execution_time,EPS.last_logical_reads,EPS.last_physical_reads ,EPS.last_logical_writes,EQS.total_worker_time,@@ROWCOUNT FROM sys.dm_exec_cached_plans ECP (NOLOCK) CROSS APPLY sys.dm_exec_sql_text (ECP.plan_handle) EST CROSS APPLY sys.dm_exec_procedure_stats EPS CROSS APPLY sys.dm_exec_query_stats EQS WHERE ECP.cacheobjtype = ''Compiled Plan''AND ECP.objtype = ''Proc''AND EST.[text] NOT LIKE ''%FOR DBA USE ONLY%''

    AND EST.[text] LIKE' + ' ''' + @ProcName + '''' -- Clear the Procedure Cache for any pre-existing cached plans for the Procedure being tested -- EXEC('INSERT INTO ' + @DeveloperSQLResults + 'EXEC sp_executesql ''' + REPLACE(@SQL,'''','''''') + '''')



    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Tuesday, November 20, 2012 4:05 PM