Using sp_executesql with params complains of the need to declare a variable

Question

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 procedures/sql server functions in general so I'm guessing I'm missing something simple. The stored procedure alter happens fine, but when I try run it I'm getting an error.

The error says.

Msg 1087, Level 15, State 2, Line 3
Must declare the table variable "@atableName"

The procedure looks like this.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

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 @aTableIDField FROM @atableName';
    EXEC sp_executesql @SQLString,
            N'@atableName varchar(50),
              @atableIDField varchar(50),
              @atableValueField varchar(50)',
            @atableName = @tableName,
            @atableIDField = @tableIDField,
            @atableValueField = @tableValueField;
END

And I'm trying to call it with something like this.

EXECUTE sp_TEST 'PERSON', 'PERSON.ID', 'PERSON.VALUE'

This example isn't adding anything special, but I have a large number of views that have similar code. If I could get this stored procedure working I could get a lot of repeated code shrunk down considerably.

Thanks for your help.

Edit: I am attempting to do this for easier maintainability purposes. I have multiple views that basically have the same exact sql except the table name is different. Data is brought to the SQL server instance for reporting purposes. When I have a table containing multiple rows per person id, each containing a value, I often need them in a single cell for the users.

Answer 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