One stored procedure that performs Update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

One stored procedure that performs Update

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-03-07 : 08:49:05

Hello all,
how can I write an only stored procedure that performs an Update in a table for only one field at time?

For exampl I have a table like this:

IDTable - Field1 - Field2 - Field3 - Field4

and only one field is passed each time.
I'd prefer not write one stored procedure for field, like:

create procedure dbo.PerformUpdateForField1
@IDTable int
@Field1 nvarchar(max)
as
UPDATE dbo.MyTable
set Field1 = @Field1
Where IDTable = @IDTable

Go

create procedure dbo.PerformUpdateForField2
@IDTable int
@Field2 nvarchar(max)
as
UPDATE dbo.MyTable
set Field2 = @Field2
Where IDTable = @IDTable

go

etc

Is it possible to write just one stored procedure for this table?

Thanks a lot.

Luigi

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-07 : 09:04:43

You can build the logic in to the stored proc a few different ways. For example, you can pass in a field id of 1,2,3, or 4 and choose the column to be updated based on that. All four columns must be of the same data type for that to work. Alternatively, you could have four arguments, one for each column and pass in an impossible value (NULL for example) for those columns you don't want to update. The first approach I described can be implemented like this:

CREATE PROCEDURE dbo.PerformUpdateForField1
	@IDTable INT,
	@Field NVARCHAR(MAX),
	@FieldId INT
AS

UPDATE dbo.MyTable
SET
	Field1 = CASE WHEN @FieldId = 1 THEN @Field ELSE Field1 END,
	Field2 = CASE WHEN @FieldId = 2 THEN @Field ELSE Field2 END,
	Field3 = CASE WHEN @FieldId = 3 THEN @Field ELSE Field3 END,
	Field4 = CASE WHEN @FieldId = 4 THEN @Field ELSE Field4 END
WHERE  IDTable = @IDTable;

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-03-07 : 09:25:52

Thank you Sunita, I'll try with NULL values.

Luigi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources