JamesBB JamesBB - 1 month ago 6
SQL Question

Query with variable tablename

I don't know why I keep bumping into these, but I have a small problem with a stored procedure. The only special in it is the variable Tablename:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE dbo.ProdTijdCompare

@TABLENAME SYSNAME,
@scanner nvarchar(50)
AS
BEGIN

SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL = 'select SUM(tijd) from ' + @TABLENAME + 'where Scanner = @scanner'
EXEC sp_executesql @SQL;
END
GO


The error I'm getting when executing in SSMM:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '='.

(1 row(s) affected)

TT. TT.
Answer

Always quote table names in dynamic SQL to avoid SQL Injection (cf QUOTENAME)

Second, supply the @scanner parameter to the sp_executesql procedure as you can see in the sample below:

SELECT @SQL = 'select SUM(tijd) from ' + QUOTENAME(@TABLENAME) + ' where Scanner = @scanner'
EXEC sp_executesql @SQL, N'@scanner NVARCHAR(50)', @scanner;
Comments