Conditionals in Update Statements

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

Transact-SQL (2000)

Conditionals in Update Statements

Author

Topic

gonorato
Starting Member

1 Post

Posted - 2002-03-06 : 18:30:39

Hello,

I am not sure if this is even possible but I hope someone will be able to offer some insite on it.

I have a stored procedure to update a table which has about 35 columns. Aside from the key, any combination of other fields could be passed in to the stored procedure as parameters. ie. @name @address etc. Every field has a default value of either "" or NULL depending on type.

Here is my question. Is there an easy way to page through the parameters in the procedure to see what has been passed a value and what hasn't. I only want to perform updates against what has been passed and not those parameters that have "" or NULL values. I am trying to avoid doing an:
IF @parameter <> ""
for each field and building an SQL out of the results. It is bulky and seems the wrong way to go.

Forgive me if there is an obvious answer I have not thought of. Thanks!

G

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-03-06 : 18:59:17

gonorato,

A common problem indeed....

First up for the following to work simply, make all your defaults NULL for the Parameters and not a mixture of '' and NULL.

Then it is a simple matter of using the COALESCE function...

eg.


create proc upMessages_Update
--Update a record from Messages
@MessageID int, --PK
@MessageDate datetime = null,
@Message varchar(1000) = null
As
Set NoCount On
Update Messages Set [MessageDate] = COALESCE(@MessageDate,MessageDate), [Message] = COALESCE(@Message,Message) Where MessageID = @MessageID

HTH

DavidM

Tomorrow is the same day as Today was the day before.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources