CODEWITHSUNDEEP
jquerysql-serverdatabasesql-server-2008-r2
Marco Dinatsoli
Marco Dinatsoli

Reputation: 10580

update field that could be null in stored procedure

I have a table that contains two columns which could be null.

I want to build a stored procedure to edit that table. in the stored procedure I did this:

@ID INT,
    @newBuildingName NVARCHAR(250),
    @newLocation NVARCHAR(3000),
    @newImage VARBINARY(MAX) = NULL,
    @newNote NVARCHAR(3000) = NULL,
    @result BIT OUTPUT

Not I can't decide in this point if the user has entered values for newImage or newNote fields, so I can't say 

UPDATE myTable SET image = @newImage, note = @newNote

because these two fields might already have values and in this way I would be deleting those vaules,

what should I do?

What I thing is make an if-else statement and try if the first field is null so make my update without it and then if my second field is null then make my update statement without it and if neither of them is null then make a full update statement but I thing that this is a very teduis process.

Is there any other solution plesae ?

Thanks in advance

Upvotes: 0

Views: 96

Answers (3)

Gruff Bunny
Gruff Bunny

Reputation: 27976

You could use the coalesce function:

UPDATE myTable 
SET 
    image = COALESCE(@newImage,image),
    note = COALESCE(@newNote,note)

If image is not null then it won't be changed. Same goes for the note column

Upvotes: 1

Grantly
Grantly

Reputation: 2556

Try this:

UPDATE myTable 
SET image = CASE WHEN image IS NULL THEN @newImage ELSE image END
,note = CASE WHEN note IS NULL THEN @newNote ELSE note END

of course you may want to use the ISNULL function also...for example

note = CASE WHEN ISNULL(note,'') = '' THEN @newNote ELSE note END

to cope with both note IS NULL and note equal to '' (empty string)

Upvotes: 0

Manikandan Sigamani
Manikandan Sigamani

Reputation: 1984

Make the if-else statements in such a way you can decide on the entities you want to update without actually committing the update.

You must already have the value of the entire entity in your code, say in your model. Please check for the following scenario.

if(!String.IsNullOrEmpty(@newImage))
{
     //include the values @newImage to the entity to update
}
if(!String.IsNullOrEmpty(@newNote))
{
     //include the values @newNoteto the entity to update
}
EntityToUpdate.saveChangestoDB();

Upvotes: 1

Related Questions