December 3, 2007 at 5:16 pm
Hi, i have a table that has 8 columns, updating into that table, but the parameters that are being sent to the procedure are dynamic, sometimes i'm sending:
lname
fname
mname
other times:
lname
mname
age
so i'm wondering how in the procedure to hadle for this:
(below everything is varchar(50) cause i'm storing the info as encrypted string.
CREATE PROCEDURE dbo.sp_insertMCpage1
(
@MemberID int,
@lname varchar(50),
@fname varchar(50),
@mname varchar(50),
@dobYear varchar(50),
@dobMonth varchar(50),
@dobDay varchar(50),
@age varchar(50),
)
AS
SET NOCOUNT ON
update MCPage1
set lname=@lname,fname=@fname,mname,=@mname,dobYear=@dobYear,dobMonth=@dobMonth,dobDay=@dobDay,age=@age)
So how can i build the procedure so that i do not update the table overwritting a value when the value being passed, for example: lname, might be null. In general i want to update the table only where there are not null values. I hope this makes sense, this is kinda what i was thinking, but i'm sure there is a better way to accomplish this.. is there?
CREATE PROCEDURE dbo.sp_insertMCpage1
(
@MemberID int,
@lname varchar(50),
@fname varchar(50),
@mname varchar(50),
@dobYear varchar(50),
@dobMonth varchar(50),
@dobDay varchar(50),
@age varchar(50),
)
AS
BEGIN
IF(@lname != null)
BEGIN
update MCPage1 set lname=@lname where MemberID=@MemberID
END
IF(@fname != null)
BEGIN
update MCPage1 set fname=@fname where MemberID=@MemberID
END
IF(@mname != null)
BEGIN
update MCPage1 set mname=@mname where MemberID=@MemberID
END
IF(@dobYear != null)
BEGIN
update MCPage1 set dobYear=@dobYear where MemberID=@MemberID
END
.... and so on and so on...
END
GO
December 3, 2007 at 5:49 pm
Use coalesce to have all this in one statement....
somethinglike this...
update MCPage1 set
lname=coalesce(@lname,lname) ,
fname=coalesce( fname,fname)
where MemberID=@MemberID
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
December 3, 2007 at 6:18 pm
ok, thanks, what you have there is stating that if the @parameter is null then i'm using the old column value instead, is that correct?
Thanks
December 3, 2007 at 6:54 pm
i leave it to you to check...lemme know if any problem
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply