June 9, 2008 at 3:10 pm
I am writing a proc that updates a single record. The proc will accept multiple params corresponding to the table columns. What is the best way to write the proc given that not *all* the params will always be passed?
e.g. should i use multiple IF statements??
IF @CategoryID IS NOT NULL
UPDATE Vids SET CategoryID = @CategoryID WHERE VidID = @VidID
IF @VidFileName IS NOT NULL
UPDATE Vids SET VidFileName = @VidFileName WHERE VidID = @VidID
etc...
this means a lot of IF statements..!
June 9, 2008 at 3:24 pm
Hey Matt,
Have you considered updating all columns at once?
[font="System"]UPDATE Vids
SET CategoryID = COALESCE(@CategoryID, CategoryID),
VidFileName = COALESCE(@VidFileName, VidFileName),
...
WHERE VidID = @VidID[/font]
Thanks,
Eric
June 9, 2008 at 3:39 pm
I'm sure someone will find a reason why this isn't a good idea, but one way would be:
CREATE PROC proc1 (@param1 int = null, @param2 varchar(1) = null, @param3 datetime = null)
UPDATE table1 SET
col1 = coalesce(@param1,col1)
,col2 = coalesce(@param2,col2)
,col3 = coalesce(@param3,col3)
[...etc]
This assumes that you will never want to update a column to NULL - but your question kind-of presupposes that, since from inside a procedure you can't tell whether a NULL was explicitly passed in or was supplied as the default parameter value.
If this isn't quite whatyou're after, a CASE statement would provide greater flexibility:
CREATE PROC proc1 (@param1 varchar(10) = null, @param2 varchar(10) = null, @param3 varchar(10) = null)
UPDATE table1 SET
col1 = CASE WHEN @param1 IS NULL THEN col1 WHEN @param1 = 'N/A' THEN col1 ELSE @param1 END
,col2 = CASE WHEN @param2 IS NULL THEN col2 WHEN @param2 = 'N/A' THEN col2 ELSE @param2 END
,col3 = CASE WHEN @param3 IS NULL THEN col3 WHEN @param3 = 'N/A' THEN col3 ELSE @param3 END
Obviously the key move is having SET col1 = col1 as one of the possible outcomes. This is not exactly equivalent to not updating the column at all, as it may for example show up in a trigger as one of the 'updated' columns, or possibly - I haven't checked this - cause the row modification counter (if that still exists in SQL9) to be incremented even though no values in the row were changed.
[edit: ah, I see someone else got in first...]
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 9, 2008 at 3:39 pm
Wow - thanks!! didn't know about COALESCE. YOU GUYS ROCK!
June 9, 2008 at 3:45 pm
COALESCE with only two parameters is equivalent to ISNULL. I always use coalesce, as it renders isnull redundant, and can be used with more params (it works from left to right and returns the first non-null value it encounters.)
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply