Using Sp_executesql With Params Complains Of The Need To Declare A Variable

I am attempting to make a stored procedure that uses sp_executesql. I have looked long and hard here, but I cannot see what I am doing incorrectly in my code. I'm new to stored p

Solution 1:

You can not parameterise a table name, so it will fail with @atableName

You need to concatenate the first bit with atableName, which kind defeats the purpose fo using sp_executesql

Solution 2:

This would work but is not advisable unless you are just trying to learn and experiment.

ALTER PROCEDURE [dbo].[sp_TEST]
    @tableName varchar(50),
    @tableIDField varchar(50),
    @tableValueField varchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQLString nvarchar(500);
    SET @SQLString = N'SELECT DISTINCT ' + quotename(@TableIDField) + ' FROM ' + quotename(@tableName);
    EXEC sp_executesql @SQLString;
END

Read The Curse and Blessings of Dynamic SQL

Solution 3:

You cannot use variables to pass table names and column names to a dynamic query as parameters. Had that been possible, we wouldn't actually have used dynamic queries for that!

Instead you should use the variables to construct the dynamic query. Like this:

SET @SQLString = N'SELECT DISTINCT ' + QUOTENAME(@TableIDField) +
                  ' FROM ' + QUOTENAME(@TableName);

Parameters are used to pass values, typically for use in filter conditions.