使用带有参数的 sp_executesql 抱怨需要声明一个变量 [英] Using sp_executesql with params complains of the need to declare a variable

问题描述

我正在尝试创建一个使用 sp_executesql 的存储过程.我在这里看了很长时间，但我看不到我在代码中做错了什么.我一般是存储过程/sql server 函数的新手，所以我猜我错过了一些简单的东西.存储过程 alter 发生得很好，但是当我尝试运行它时出现错误.

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.

错误提示.

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

程序如下所示.

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.

感谢您的帮助.

我试图这样做是为了更容易维护.我有多个视图，除了表名不同外，它们基本上具有相同的 sql.数据被带到 SQL 服务器实例以用于报告目的.当我的表中每个人的 id 包含多行，每行都包含一个值时，我经常需要将它们放在一个单元格中供用户使用.

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.

推荐答案

这可行但不建议，除非您只是想学习和实验.

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

阅读动态 SQL 的诅咒和祝福

这篇关于使用带有参数的 sp_executesql 抱怨需要声明一个变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！