June 19, 2009 at 8:30 am
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?
June 22, 2009 at 5:17 am
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
June 22, 2009 at 6:34 am
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.
June 23, 2009 at 4:58 pm
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