Subtleties of exec in TSQL

Question

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?

Answer 1

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 ' ]
[;]