This probably isn't news to anyone, but I thought I'd document a nifty way of allowing a single stored procedure to update an arbitrary number of columns at once, without disturbing columns that don't need to be updated.Given a table:USERS-----user_id int IDENTITYfield1 intfield2 varchar(200)field3 datetime...Here's a stored procedure:CREATE PROCEDURE p_UpdateUser (@iUsers int,@f1 int=NULL,@f2 varchar(200)=NULL,@f3 datetime=NULL) ASset nocount onupdate users set field1=IsNull(@f1,field1),field2=IsNull(@f2,field2),field3=IsNull(@f3,field3)
Now, an ASP page or other app can update any combination of columns using a single stored procedure, without resorting to dynamic SQL.Examples:p_UpdateUser @iUsers=1,@f2='test'p_UpdateUser @iUsers=1,@f1=47,@f3='5/5/2002 17:00:00'
Probably nothing special or new, but I thought it was nifty. You could do the same thing with COALESCE rather than ISNULL, of course, but unless there's some advantage ISNULL is more intuitive to read.Cheers-bNo problem! -robvolkEdited by - robvolk on 05/05/2002 17:26:06
Tip: updating arbitrary columns using SP
Posted - 2002-05-05 : 16:43:21
