存储过程出错

Question

我正在尝试在SQL Server 2008上创建一个存储过程，出现错误：

Msg 156，级别 15，状态 1，程序 usp_UpdateDistribiutionList， 第 12 行 关键字"AS"附近的语法不正确。

你能建议吗？

存储过程代码：

USE LogDB
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE usp_UpdateDistribiutionList 
-- Add the parameters for the stored procedure here
@distId INT,
@DistEmails varchar(max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE [NotificationDistribution] AS dist
SET dist.emailCC = @DistEmails
WHERE dist.DistributionID = @distId
END

第二个问题：

是否可以使用参数来获取像dist.@columnName这样的列名？

Answer 1

删除 UPDATE 语句中的AS，并将其放在 FROM 子句中，它应该是：

UPDATE dist
SET dist.emailCC = @DistEmails
FROM [NotificationDistribution]  AS dist
WHERE dist.DistributionID = @distId

---

对于你的第二个问题，你不能那样做，你必须使用动态SQL动态地做到这一点。

Answer 2

将更新更改为以下内容：

UPDATE [NotificationDistribution]
SET emailCC = @DistEmails
WHERE DistributionID = @distId

如果你想做动态：

CREATE PROCEDURE usp_UpdateDistribiutionList 
-- Add the parameters for the stored procedure here
@distId INT,
@DistColumn varchar(max),
@DistValue varchar(max)
AS
BEGIN

    SET NOCOUNT ON;
    DECLARE @SQL AS NVARCHAR(MAX)
    SET @SQL = 
    N'UPDATE [NotificationDistribution]
    SET ' + QuoteName(@DistColumn ) + ' = @DistValue 
    WHERE DistributionID = @distId'
    EXECUTE sp_executesql 
    @SQL,
    N'@DistValue  varchar(max), @distId INT',
    @DistValue  = @ColumnValue,
    @distId = @distId
END