DynamicSQL using sp_executesql Error

Question

I keep getting an error with the following stored procedure. I had it working correctly using EXEC, then I switched to sp_executesql and I haven't been able to get it to execute. I keep getting the following error: Incorrect syntax near '@numberOfItems'.

ALTER PROCEDURE dbo.FetchResourcesToProcess
(
@tableName nvarchar(MAX),
@numberOfItems int
)
AS
    BEGIN
        DECLARE @SQL nvarchar(MAX);
        SET NOCOUNT ON;
        SET @SQL = N'Select TOP @numberOfItems * from ' + @tableName + N' where Active = 1 AND BeingProcessed = 0'
        EXEC sp_executesql @SQL, N'@numberOfItems int', @numberOfItems
    END

Tablename is a string structured as follows: "[TABLENAME]".

Thanks

Do a select @SQL. Take the result and copy and paste it in SSMS and see if you can execute that. You'll get better error messaging. — JeffO, Jul 17 '12 at 23:11
You need to put parentheses around @numberOfItems: `N'Select TOP (@numberOfItems) * from '` — Nikola Markovinović, Jul 17 '12 at 23:20

score 4 · Answer 1 · answered Jul 17 '12 at 23:07

4

You probably need to place number of items into the string the same way you are the table name SET @SQL = N'Select TOP ' + Convert(varchar(10),@numberOfItems) + ' * from ' + @tableName + N' where Active = 1 AND BeingProcessed = 0'

answered Jul 17 '12 at 23:07

automatic

2,727
3
34
31

score 0 · Answer 2 · answered Nov 18 '16 at 11:47

I think you can only use parameters for sp_executesql statement in positions where variables are allowed.

use master;
declare @numberOfItems  int;
set @numberOfItems  =   2;
Select TOP @numberOfItems * from dbo.spt_values

Incorrect syntax near '@numberOfItems'.

use master;
declare @table  varchar(max);
set @table  =   'dbo.spt_values';
Select * from @table

Must declare the table variable "@table".

use master;
declare @numberOfItems  int;
set @numberOfItems  =   2;
Select TOP(@numberOfItems) * from dbo.spt_values

(2 row(s) affected)

Solution 1 (parenthesis, recommended):

        SET @SQL = N'Select TOP(@numberOfItems) * from ' + @tableName + N' where Active = 1 AND BeingProcessed = 0'

Solution 2 (concatenation, make sure to prevent SQL injection!):

        SET @SQL = N'Select TOP '+cast(@numberOfItems as nvarchar(MAX))+' * from ' + @tableName + N' where Active = 1 AND BeingProcessed = 0'
        EXEC sp_executesql @SQL

DynamicSQL using sp_executesql Error

2 Answers2