?
Solved

Generic update Stored Procedure for Table

Posted on 2014-04-07
5
Medium Priority
?
614 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 143

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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

777 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