I have been working on a few stored procedures that have conditional parameters to them, but one of them is giving me a problem that I can't quite figure out. This is the code for the procedure:
CREATE PROCEDURE dbo.GetTableData(
@TblName VARCHAR(50),
@Condition VARCHAR(MAX) = NULL,
) AS
BEGIN
IF(EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TblName))
BEGIN
DECLARE @SQL NVARCHAR(MAX) = N'
SELECT * FROM @TblName WHERE 1=1'
+ CASE WHERE @Condition IS NOT NULL THEN
' AND ' + @Condition ELSE N'' END
DECLARE @params NVARCHAR(MAX) = N'
@TblName VARCHAR(50),
@Condition VARCHAR(MAX)';
PRINT @SQL
EXEC sys.sp_executesql @SQL, @params,
@TblName,
@Condition
END
ELSE
RETURN 1
END
The way I would like the procedure to work, is that it's suppose to allow me to do quick table look ups. So if I want to see everything from my Parts table, I'd just run
EXEC GetTableData 'parts'
Or if I wanted to see everything in the Parts table with a specific supplier I'd run
EXEC GetTableData 'parts', 'supplier LIKE ''A2A Systems'''
Now in the above example, when I run it, the PRINT @SQL
line prints out the query as:
SELECT * FROM @TblName WHERE 1=1 AND supplier LIKE 'A2A Systems'
So the query it being put together properly (it seems).
However after it prints I am getting the following error:
Msg 1087, Level 16, State 1, Line 4
Must declare the table variable "@TblName"
I still get this error if I change the EXEC
line to:
EXEC GetTableData @TblName='parts', @Condition='supplier LIKE ''A2A Systems'''
So what am I doing wrong here? Why isn't it taking my @TblName
variable value?
Best Answer
You need to modify your procedure this way:
Your variable @TblName must not be inside the @SQL string