Subtleties of exec in TSQL

exec, sql-server, stored-procedures, t-sql

Solution

Use parenthesis in your exec

EXEC (@SQL);

EXECUTE (Transact-SQL)

Without the parenthesis you are using this:

Execute a stored procedure or function
[ { EXEC | EXECUTE } ]
    { 
      [ @return_status = ]
      { module_name [ ;number ] | @module_name_var } 
        [ [ @parameter = ] { value 
                           | @variable [ OUTPUT ] 
                           | [ DEFAULT ] 
                           }
        ]
      [ ,...n ]
      [ WITH  [ ,...n ] ]
    }
[;]

You want this where the parenthesis is required.

Execute a character string
{ EXEC | EXECUTE } 
    ( { @string_variable | [ N ]'tsql_string' } [ + ...n ] )
    [ AS { LOGIN | USER } = ' name ' ]
[;]

Problem

This stored procedure ``` CREATE PROC GetPage(@blockNumber int, @blockSize int = 40, @query varchar(1000)) AS DECLARE @a int = @blockNumber * @blockSize; DECLARE @b int = @a + @blockSize - 1; DECLARE @fromPos int = PATINDEX('% FROM %', @query); DECLARE @from varchar(1000) = SUBSTRING(@query, @fromPos, 1000); DECLARE @select varchar(1000) = SUBSTRING(@query, 1, @fromPos); DECLARE @SQL varchar(1000) = 'select *, ROW_NUMBER() over (order by ONE) R INTO #FOO FROM (' +@SELECT+',1 ONE'+@from+') T'; EXEC @SQL; SELECT * FROM FOO WHERE RN BETWEEN @a AND @b; DECLARE @C INT = (SELECT COUNT(*) FROM #FOO); DROP TABLE #FOO RETURN @C; ``` when passed `SELECT * FROM ASSET` generates this SQL ``` select *, ROW_NUMBER() over (order by ONE) R INTO #FOO FROM (select * ,1 ONE from asset) T ``` When I execute this from SQL Server Management Studio, like this: ``` exec('select *, ROW_NUMBER() over (order by ONE) R INTO #FOO FROM (select * ,1 ONE from asset) T') ``` it creates the table #FOO as expected. However, when the stored procedure is executed: ``` exec getpage 5,10,'select * from asset' ``` I get this error Msg 2812, Level 16, State 62, Procedure GetPage, Line 12 Could not find stored procedure 'select *, ROW_NUMBER() over (order by ONE) R INTO FOO FROM (select * ,1 ONE from asset) T'. Msg 208, Level 16, State 1, Procedure GetPage, Line 14 Invalid object name '#FOO'. I think the second message is merely a consequence of the first error. Does anyone know why the `exec` statement is behaving differently inside a stored procedure?

Original source