Updating a Table through a stored procedure with v

问题:

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

回答1:

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

回答2:

I know more of postgres than sql server, may be this statement below can help you out.

UPDATE table1 SET Column1 = (CASE WHEN (Column1 IS NULL || Column1==@Variable1) THEN @Variable1 else Column1),
Column2 = (CASE WHEN (Column2 IS NULL || Column2 == @Variable2) THEN @Variable2 else Column2),
Column3 = (CASE WHEN (Column3  IS NULL || Column3 == @Variable3) THEN @Variable3 else Column3)