CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
user1603398
user1603398

Reputation: 77

Dynamic stored procedure to update a table

An application developer has asked me to create three different stored procedures to update the same table. Each stored procedure updates different columns and are called at different points in their application. I know there must be a way to create one combined stored procedure that could do the work.

At first, I tried to create a stored procedure with a "switch" parameter that would update the columns from each stored procedure based on the switch parameter.

CREATE PROCEDURE dbo.[Upd_table1] 
    (@switch INT, @ID INT, 
     @col1 INT, @col2 INT, @col3 INT, @col4 INT, @col5 INT,
     @col6 INT, @col7 INT, @col8 INT, @col9 INT)
AS
BEGIN
    IF (@switch = 1)
        UPDATE dbo.table1
        SET col1 = @col1, col4 = @col4, col5 = @col5, col6 = @col6
        WHERE ID = @ID
    ELSE IF (@switch = 2)
        UPDATE dbo.table1
        SET col2 = @col2, col9 = @col9
        WHERE ID = @ID
    ELSE IF (@switch = 3)
        UPDATE dbo.table1
        SET col3 = @col3, col7 = @col7, col8 = @col8
        WHERE ID = @ID
END

This works as expected but I was wondering if there is a way to remove the switch and update the table with whatever parameters are supplied.

Upvotes: 0

Views: 86

Answers (2)

Laughing Vergil
Laughing Vergil

Reputation: 3756

There is a way that emulates the switch functionality based on the fact that there are no overlapping columns being updated between switches.

CREATE PROCEDURE dbo.[Upd_table1] (@switch INT, @ID INT, @col1 INT, @col2 INT, @col3 INT, @col4 INT, @col5 INT, @col6 INT, @col7 INT, @col8 INT, @col9 INT )
AS
BEGIN
    IF @Col1 IS NOT NULL AND @Col2 IS NULL AND @Col3 IS NULL -- switch = 1 equivalent
        UPDATE dbo.table1
            SET col1 = @col1, col4 = @col4, col5 = @col5, col6 = @col6
        WHERE ID = @ID
    ELSE IF @Col2 IS NOT NULL AND @Col1 IS NULL and @Col3 IS NULL -- switch = 2 equivalent
        UPDATE dbo.table1
            SET col2 = @col2, col9 = @col9
        WHERE ID = @ID
    ELSE IF @Col3 IS NOT NULL AND @Col1 IS NULL and @Col2 IS NULL -- switch = 2 equivalent
        UPDATE dbo.table1
            SET col3 = @col3, col7 = @col7, col8 = @col8
        WHERE ID = @ID
    ELSE
    -- Handle error case of incorrect parameters here
        RAISERROR('Incorrect Parameters passed to [Upd_table1]', 16, 1) -- sample error handler
END

Upvotes: -1

Bestter
Bestter

Reputation: 878

I would use COALESCE: return the first non null parameter.

SET COL1 = COALESCE(@Col1, Col1) = if @Col1 IS NULL, take current value of col1. Then, you update all your field in one operation.

UPDATE dbo.table1
        SET col1 = COALESCE(@col1,col1), col2=COALESCE(@col2,col2), col3=COALESCE(@col3,col3), etc...
        WHERE ID = @ID

Upvotes: 2

Related Questions