通过带有可变参数的存储过程更新表 [英] Updating a Table through a stored procedure with variable parameters

问题描述

为了便于使用，我有存储过程，其中包含用于 SELECT 语句的可选参数，如下所示:

For ease of use I have Stored procedures which have optional parameters for SELECT statments like the below:

CREATE PROCEDURE stpProcedure
    @Variable1 INT = NULL,
    @Variable2 INT = NULL,
    @Variable3 INT = NULL,
    @NumberToReturn INT = 1000
AS
BEGIN
    SELECT TOP (@NumberToReturn)    Column1,
                                    Column2,
                                    Column3,
                                    Column4
    From                            Table1
    WHERE                           (@Variable1 IS NULL OR Column1 = @Variable1)
    AND                             (@Variable2 IS NULL OR Column2 = @Variable2)
    AND                             (@Variable3 IS NULL OR Column3 = @Variable3)
END
GO

有没有办法将这种技术用于更新语句?即我想根据特定表的存储过程输入更新一个或多个列.我正在尝试做的一个例子如下:

Is there a way to use this technique for update statements? Ie I want to update one or more columns based on the stored procedure input for a particular table. An example of what I am trying to do is below:

CREATE PROCEDURE stpUpdate
    @Variable1 INT = NULL,
    @Variable2 INT = NULL,
    @Variable3 INT = NULL,
    @Variable4 INT,
    @NumberToReturn INT = 1000
AS
BEGIN
    Update      Table1
    SET         Column1 = @Variable1,
                Column2 = @Variable2,
                Column3 = @Variable3
    From        Table1
    Where       Column4 = @Variable4
END
GO

推荐答案

您可以使用 COALESCE() 函数来构建这种更新语句

You can use COALESCE() function to build this kind of update statement

CREATE PROCEDURE stpUpdate
    @Variable1 INT = NULL,
    @Variable2 INT = NULL,
    @Variable3 INT = NULL,
    @Variable4 INT,
    @NumberToReturn INT = 1000
AS
BEGIN
    Update      Table1
    SET         Column1 = COALESCE(@Variable1,Column1),
                Column2 = COALESCE(@Variable2,Column2),
                Column3 = COALESCE(@Variable3,Column3)

    Where       Column4 = COALESCE(@Variable4,Column4)
END
GO

这篇关于通过带有可变参数的存储过程更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！