asked on

Generic update Stored Procedure for Table

Hi all,

I have an application where I am using table adapters with MSSQL.

I want to create an update method whereby I can update only the passed variables;

for example take the following table;

userTable
id
name
surname
email
username

i would like to create a generic update method whereby i may only pass the email and the id to update the surname. (To not the update method on my table adapter is not created automatically as it has a join across two tables).

Is it better to use the table adapter or would you recommend creating a stored procedure for this?

Would it be possible for an example update stored procedure in this case as SQL is not my strong point.

Thanks.

Pratima

create procedure UpdateMyTable
@id int,
@ColName varchar(25),
@ColValue varchar(25)

as
update MyTable
set name = case when @ColName = 'name' then @ColValue else name end,
set surname = case when @ColName = 'surname' then @ColValue else surname end,
set email = case when @ColName = 'email' then @ColValue else email end,
set username = case when @ColName = 'username' then @ColValue else username end
where id = @id

Guy Hengel [angelIII / a3]

if the only target is to handle a "non-update" in the trigger behind in regards to the UPDATE() function, then such a "generic" procedure (dynamic sql) would make sense.

if the issue would be concurrent access, you shall need to handle concurrent access in general, and not try to update only parts of the columns.

in any other cases, just update all the column values.

note: the above sql is not recommendable:
* it will update all the columns nevertheless, though it will reuse the current value for the column.
* the data type of @colvalue would be varchar, though your actual table column may be numerical, date, bit, XML, binary etc, so far from generic.

flynny

ASKER

Pratima,

thanks for this.

sorry for not explaining correctly. I was looking for a stored procedure whereby I can send any combination of params passed to it so e.g.

userTable
userID
username
firstname
middlename
surname
email

so I can then for example update(null, null 'middlename', null, 'email');

just updating the middlename and email, as null was passed it would ignore the update on these fields?

Pratima

change the datatypes as per your 's

create procedure UpdateMyTable

@userID int ,
@username varchar(25),
@firstname varchar(25),
@middlename varchar(25),
@surname varchar(25),
@email varchar(25)

as
update userTable
set username = case when @username is not null then @username else username end,
set firstname = case when @firstname is not null then @firstname else firstname end,
set middlename = case when @middlename is not null then @middlename else middlename end,
set surname = case when @surname is not null then @surname else surname end,
set email = case when @email = is not null then @email else email end,
set username = case when @ColName = 'username' then @ColValue else username end
where id = @userID

ASKER CERTIFIED SOLUTION

Pratima

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial