Best way to code a Stored proc to run an update with a varying number of parameters?

  • I have a stored proc which is going to update multiple columns (18 possible columns in total) in a table. I have parameters holding the value of each column that can be updated. I only want to pass in parameters where a value is to be changed, so I've given each parameter a default of NULL. This all works, but I'm wondering what the best/optimized/neatest way to actually code the update statement would be.

    The ways I've come up with are:

    Update every column regardless of whether a new value is being passed in. To do this I've used a CASE for each column in the UPDATE statement and where there is no new value to set (parameter IS NULL), I've just set it equal to what it already is. (This seems like a waste of resources when only 1 column is to be updated).

    CREATE PROCEDURE [dbo].[usp_UpdateTable]

    @PK_TableID int,

    @FK_ID int = NULL,

    @somecol nvarchar(15) = NULL

    ...

    AS

    SET NOCOUNT ON;

    UPDATE dbo.MyTable

    SET FK_ID = CASE WHEN @FK_ID IS NULL THEN FK_ID ELSE @FK_ID END,

    somecol = CASE WHEN @somecol IS NULL THEN somecol ELSE @somecol END,

    ...

    WHERE PK_TableID = @PK_TableID

    Dynamic sql to construct the update statement depending on the values of the parameters being passed in. (This one better in that it only updates the necessary columns, but is not as easy to read).

    CREATE PROCEDURE [dbo].[usp_UpdateTable]

    @PK_TableID int,

    @FK_ID int = NULL,

    @somecol nvarchar(15) = NULL

    ...

    AS

    SET NOCOUNT ON;

    DECLARE @sql varchar(4000)

    SET @sql = '

    UPDATE dbo.MyTable SET '

    IF @FK_ID IS NOT NULL

    SET @sql = @sql + 'FK_ID = ' + CONVERT(varchar,@FK_ID ) + ', '

    IF @somecol IS NOT NULL

    SET @sql = @sql + 'somecol = ' + @somecol + ', '

    ...

    --Check that at least something is going to be updated

    IF RIGHT(@SQL,2) = 'T ' --no fields are to be updated

    RETURN

    --Remove the final ', '

    SET @sql = LEFT(@SQL,LEN(@SQL)-1)

    --Add final WHERE clause

    SET @sql = @sql + ' WHERE PK_TableID = ' + CONVERT(varchar,@PK_TableID)

    EXEC(@SQL)

    An IF condition to test whether a new value has been passed in for each parameter and then use a seperate update statement to update each field in turn. (This too seems like a lengthy option as I'd need to add code to deal with rolling back the transaction should one of the updates fail. Plus I'm not sure of how optimized it is to perform multiple update statements).

    CREATE PROCEDURE [dbo].[usp_UpdateTable]

    @PK_TableID int,

    @FK_ID int = NULL,

    @somecol nvarchar(15) = NULL

    ...

    AS

    SET NOCOUNT ON;

    IF @FK_ID IS NOT NULL

    UPDATE dbo.MyTable

    SET FK_ID = @FK_ID

    WHERE PK_TableID = @PK_TableID

    IF @somecol IS NOT NULL

    UPDATE dbo.MyTable

    SET somecol = @somecol

    WHERE PK_TableID = @PK_TableID

    ...

    I've also seen people using a single parameter of data type XML which contains all the columns, and their new values, to be updated. But again a seperate update statement has been used for each element within the XML.

    Does anyone have any other ideas on how to achieve this?

  • Hi,

    Try the another method

    Create procedure UPDATE_TABLE

    (

    @PK_TableID int,

    @para1 int = NULL,

    @para2 nvarchar(15) = NULL

    ...

    as

    begin

    set nocount on

    update dbo.MyTable

    set Col1 = isnull(@para1,Col1),

    Col2 = isnull(@para2,Col2),

    Col3 = isnull(@para3,Col3)

    ...

    ...

    where TableID = @PK_TableID

    ARUN SAS

  • Hi arun,

    Yeah, that's pretty much the same method as my first example, in the way that you are updating all columns regardless of whether they need updating (ie, you are setting them equal to their current value if null). I'm sure there is some overhead in doing it this way. I did some testing using STATISTICS TIME and it appears that most of the time it takes a lot longer updating all columns rather than just the one(s) you need. Pretty obvious I suppose! But I was kinda hoping there was a neater way to do the update other than dynamic SQL. I'm currently using the dynamic SQL method and will stick with that for the time being.

  • Steve: Here is a variation on your dynamic SQL example:

    CREATE PROCEDURE [dbo].[usp_UpdateTable]

    @PK_TableID int,

    @FK_ID int = NULL,

    @somecol nvarchar(15) = NULL

    ...

    AS

    SET NOCOUNT ON

    if @FK_ID is not null or

    @somecol is not null -- ... BEGIN

    DECLARE @sql varchar(4000)

    SET @sql = 'UPDATE dbo.MyTable SET PK_TableID = PK_TableID' +

    case when @FK_ID IS NULL then ''

    else ', FK_ID = ' + CONVERT(varchar,@FK_ID )

    end +

    case when @somecol IS NULL then ''

    else ', somecol = ' + quotename(@somecol,'"') + ''

    end +

    --...

    ' WHERE PK_TableID = ' + CONVERT(varchar,@PK_TableID)

    EXEC (@SQL)

    END

    The quotename function may be important if any of the character data might include quote marks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply