Using sp_executesql with params complains of the n

问题:

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.

回答1:

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

回答2:

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

回答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.