Solved

Generic update Stored Procedure for Table

Posted on 2014-04-07
5
526 Views
Last Modified: 2014-04-08
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.
0
Comment
Question by:flynny
  • 3
5 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39982689
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
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39982749
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.
0
 

Author Comment

by:flynny
ID: 39982776
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?
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39982791
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
0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 39982792
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 user id = @userID
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now