Solved

Generic update Stored Procedure for Table

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

776 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