Solved

Generic update Stored Procedure for Table

Posted on 2014-04-07
5
512 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

743 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

11 Experts available now in Live!

Get 1:1 Help Now