Problem w/Stored Proc - SQL Server Forums

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)

Problem w/Stored Proc

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2002-11-19 : 18:02:31

I have a stored procedure which has several input parameters (basically, one for each column). There are two image columns in the table. Right now, I have set the image values with a default value of NULL. There are times when I execute the procedure that the images get passed into the SP. This works great. However, there are times when I call the SP (which is issuing an UPDATE on the table), where the image column is either not being updated or inserted for the first time. In these cases, previously, the default NULL value was writing over the existing image value/contents in the column.

To resolve this issue...for now....I just added an IF statement to check if these passed in values are NULL.....if they are not, then I issue a secondary update on the table.

My question...is this the best way to handle input parameters (that have a default value) that you don't want to update/insert in the cases where the parameters are null?

Thanks....and thanks!

rickratayczak
Starting Member

5 Posts

Posted - 2002-11-20 : 04:00:59

Yes, if this is what you are doing below, there is no better way that I know of to accomplish this.


IF @ImageField IS NULL
BEGIN
   UPDATE BLAH SET BLAHFIELD = @BLAHVALUE
END
ELSE
BEGIN
   UPDATE BLAH SET BLAHFIELD = @BLAHVALUE
END

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-20 : 04:05:25

UPDATE yourtable set field = COALESCE (@parameter, field)

?

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2002-11-20 : 11:10:55

Thanks for the replies.

To rickratayczak - Yes, that is what I am currently doing.

To mr_mist - Coalesce?? Hmmm...I am not familiar with that term...as far as its relation to sql server. I'll do some research and see if I can figure out what you are referring to.

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-20 : 11:20:47

It's saying "if @parameter is not null then set field = @parameter, otherwise set field = field (IE don't change it)". I think you can use it in this manner, if not you can use a CASE to much the same effect.

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2002-11-20 : 12:21:23

Okay...I just read the BOL too and I see what you're saying (or typing)...that Coalesce is a replacement for CASE constructs. I didn't know about that....and I like it so much I'll start replacing the existing CASE statements in my SQL expressions.

However....as for what I am trying to accomplish, Coalesce won't work. The reason, is that I am not trying to check an input parameter and then leave it or change it...I need to check in input parameter and if it IS NULL...then I don't want to update a specific column in the table.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-20 : 12:27:41

Take a look here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21323

You can use COALESCE instead of NULLIF or CASE, it will work the same way. Essentially, if the parameter is null, then the column's current value will be substituted, so you get SET col1=col1.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-20 : 12:36:55

I think it is much more efficient to use IF's because if you are updating lots of records -- whith image data which can be big -- you will be asking the server to do a lot of work it doesn't need to do. Plus, the transaction log will get entries it doesn't need.

I would think about a two-line stored proc:

UPDATE Table SET Field1 = @Param1 WHERE @Param1 Is Not Null;
UPDATE Table SET Field2 = @Param2 WHERE @Param2 Is Not Null

To be as efficient as possible, it could even be a 3-line proc:

UPDATE Table SET Field1 = @Param1
WHERE @Param1 Is Not Null and @Param2 Is Null;
UPDATE Table SET Field2 = @Param2
WHERE @Param2 Is Not Null and @Param1 Is Null;
UPDATE Table SET Field1 = @Param1,
SET Field2 = @Param2
WHERE @Param1 Is Not Null AND Param2 Is Not Null

I know it's ugly, but I don't like overwriting fields with the same data if I don't have to. Espcially fields with large contents or for lots of records.

For only 1 record though, if that's the case here, overwriting may be fine.

- Jeff

Edited by - jsmith8858 on 11/20/2002 13:08:08

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2002-11-20 : 16:35:46

The two last posts were very helpful. Currently, the SP is testing the image parameters and issuing a subsequent UPDATE after the initial update of the regular fields...which are derived from controls on a form. Therefore, it won't be over writing the contents of the image columns with the same data. The update is only running on one record at a time...but there might be several concurrent users updating records too. So, if it improves performance to not update data that isn't necessary...then I may leave things as they are.

Thanks to everyone that contributed.
...D

Lady
Starting Member

32 Posts

Posted - 2002-11-21 : 10:56:36

update table
set image = isnull(@image,image)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources