April 26, 2009 at 7:59 pm
Hi all
i have a stored procedure that looks like this
create procedure update_user
@id int,
@name nvarchar(100),
@type nvarchar(6),
@add_date date,
@email varchar(50),
@phone int
as
BEGIN
UPDATE user set
name=@name
, type=@type
, add_date=@add_date
, email =@email
, phone=@phone
WHERE
id =@id
END
What i'd like help with is a method to compare the parameters passed in with the table values and have the sp update only the changed columns.
i.e if i passed in @id=10, @name=John Smith, @type=Manager, @email=lala@lala.com, @phone=333-111-2222
and the values in the table user for id=10 are name=John Smith, type=NULL, email=abc@123.com, phone=311-222-111
then it should only update columns type email and phone.
Thanks in advance for any help
April 27, 2009 at 6:51 am
create procedure update_user
(
@id int,
@name nvarchar(100),
@type nvarchar(6),
@add_date date,
@email varchar(50),
@phone int
)
as
set nocount on
UPDATEuser
setname=@name
, type=@type
, add_date=@add_date
, email =@email
, phone=@phone
WHEREid =@id
and (
name @name
or type @type
or add_date @add_date
or email @email
or phone @phone
)
N 56°04'39.16"
E 12°55'05.25"
April 27, 2009 at 9:51 am
Peso (4/27/2009)
create procedure update_user(
@id int,
@name nvarchar(100),
@type nvarchar(6),
@add_date date,
@email varchar(50),
@phone int
)
as
set nocount on
UPDATEuser
setname=@name
, type=@type
, add_date=@add_date
, email =@email
, phone=@phone
WHEREid =@id
and (
name @name
or type @type
or add_date @add_date
or email @email
or phone @phone
)
Thanks, but wouldn't this still update all of the columns for where any of the passed in params are not equal? I would like it to update only the columns where they have changed because we have an audit database that tracks changes and it would make the process of running a report painful.
Thanks once again
April 27, 2009 at 10:02 am
If this doesn't suite your needs, your only option left is to have 5 separate updates.
UPDATEuser
SETname = CASE WHEN @name name THEN @Name ELSE Name END,
type = CASE WHEN @type type THEN @type ELSE type END,
add_date = CASE WHEN @add_date add_date THEN @add_date ELSE add_date END,
email = CASE WHEN @email email THEN @email ELSE email END,
phone = CASE WHEN @phone phone THEN @phone ELSE phone END,
WHEREid = @id
N 56°04'39.16"
E 12°55'05.25"
April 27, 2009 at 11:49 am
Yeah i guess i do need to do 5 or more updates at once then. There are some procs with 20 params so i was looking for away around the multiple updates.
Thanks though
April 27, 2009 at 1:28 pm
zsysop (4/27/2009)
Thanks, but wouldn't this still update all of the columns for where any of the passed in params are not equal? I would like it to update only the columns where they have changed because we have an audit database that tracks changes and it would make the process of running a report painful.
I'm curious as to how you have your audit tables set up. It sounds like you are inserting into a table just the columns that have been changed. What I've usually seen is for the tables being audited, that you have a duplicate table with a few extra columns for who/when/etc. Then in triggers, add rows to the audit table from the inserted and deleted logical tables. See BOL - CREATE TRIGGERfor more information about this.
The only other method that I can think of is to use dynamic sql so that you only update the changed fields. To avoid the risk of sql injection, ensure that you use the sp_executesql stored procedure with parameters for the sql statement being passed it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 27, 2009 at 3:08 pm
WayneS (4/27/2009)
zsysop (4/27/2009)
Thanks, but wouldn't this still update all of the columns for where any of the passed in params are not equal? I would like it to update only the columns where they have changed because we have an audit database that tracks changes and it would make the process of running a report painful.I'm curious as to how you have your audit tables set up. It sounds like you are inserting into a table just the columns that have been changed. What I've usually seen is for the tables being audited, that you have a duplicate table with a few extra columns for who/when/etc. Then in triggers, add rows to the audit table from the inserted and deleted logical tables. See BOL - CREATE TRIGGERfor more information about this.
This is how we have it set up currently, but trying to run a report to display what has changed is causing me some trouble.
Maybe i can do this in another way.
Here's what i am trying to accomplish.
I have a Database called DB with a table named user
USER
-------------
.id int,
name nvarchar(100),
type nvarchar(6),
add_date date,
email varchar(50),
phone int
I also have a DBHist with a table named user
USER
---------
id int,
name nvarchar(100),
type nvarchar(6),
add_date date,
email varchar(50),
phone int
audit_date datetime,
audit_stamp varchar(20)
audit_user varchar(20)
what i'd like is a procedure that will display something like
@id = 1
Display
Field Changed | From | To | By | Audit Date
------------------------------------------------------------------------
name |"old_name"(from DBHist) | name (DB) | "user" | "audit_date"
Thanks for any help
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply