CODEWITHSUNDEEP
sqldatabasestored-procedures
Munish Goyal
Munish Goyal

Reputation: 1409

Need example of Conditional update stored proc in SQL server

I am just at starting levels in DB usage and have 2 basic questions

  1. I have a generic UPDATE stored proc which updates all columns of a table.

But i need to make it conditional wherein it does not SET when the parameter is NULL.

Usage: I want to use this as a single SP to UPDATE any subset of columns, the caller from C# will fill in corresponding parameter values and leave other parameters NULL.

2

In case of , "UPDATE selected records" do i need to use locking inside stored proc ?

Why ? Isn't the operation in itself locked and transactional ?

I find the same question come up when i need to UPDATE selected(condition) records and then Return updated records.

Upvotes: 1

Views: 2086

Answers (2)

Sam Saffron
Sam Saffron

Reputation: 131192

UPDATE table SET a = case when @a is null then a else @a end WHERE id = @id

OR

EXEC 'update table set ' + @update + ' where id = ' + @id

OR

Conditionally update a column at a time

First option to me would usually be preferrable as it is usually efficient enough and you do not need to worry about string escaping

Upvotes: 2

KBBWrite
KBBWrite

Reputation: 4419

If I have understood the question properly, Why can't you build a query on the fly from sql server SP, and use sp_sqlexecute. So when you build query you can ensure only columns that have value has got updated.

Does this answer your question?

Upvotes: 2

Related Questions