MS SQL Server - update stattement doesn't update my fields...

Author

update stattement doesn't update my fields...

Jéjé

2005-11-29, 3:23 am

Hi,

I have an update statement in a storedprocedure whichis:
Update TargetDB..Table
Set
Column1 = coalesce(@Column1 ,Column1 ) ,
Column4 = @Column4 ,
Column2 = coalesce(@Column2 ,Column2 ) ,
Column3 = @Column3
.....
where TableKey = @TableKey

the column 1 and 2 are not updated when I provide a new value.
and the column4 is not updated except if I move it after the coalesce
updates!!!

what's appends?
I'm using SQL 2000.

thanks.

Jerome.

Roji. P. Thomas

2005-11-29, 3:23 am

Can you post a repro?

--
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com

"Jéjé" < willgart@BBBhotmailA
AA.com> wrote in message
news:e7VfxDK9FHA.2816@tk2msftngp13.phx.gbl...
> Hi,
>
> I have an update statement in a storedprocedure whichis:
> Update TargetDB..Table
> Set
> Column1 = coalesce(@Column1 ,Column1 ) ,
> Column4 = @Column4 ,
> Column2 = coalesce(@Column2 ,Column2 ) ,
> Column3 = @Column3
> ....
> where TableKey = @TableKey
>
> the column 1 and 2 are not updated when I provide a new value.
> and the column4 is not updated except if I move it after the coalesce
> updates!!!
>
> what's appends?
> I'm using SQL 2000.
>
> thanks.
>
> Jerome.
>
>

Jens

2005-11-29, 3:23 am

Try to print out the data of the varaibles priot sending the Update
command to check wheter the data is (first and second expression) not
containing NULLS. Something like PRINT 'The Expression is --> ' +
@Column1

HTH, jens Suessmeyer.

Jéjé

2005-11-29, 7:23 am

the print of the variables in the SP is good, I see the new value:

print @column1
print @column2
.....

print 'Before update'
Select * from table where id=@id

update.....

print 'After update'
Select * from table where id=@id

the second select statement display that coealesce columns are not updated
with the new value.
if I change coalesce for isnull(@column1, column1) the result is the same.
the print commands display the right values.
when I remove the coalesce function and update directly, the statement works
fine!!!

"Jens" <Jens@sqlserver2005.de> wrote in message
news:1133248760.044990.115790@o13g2000cwo.googlegroups.com...
> Try to print out the data of the varaibles priot sending the Update
> command to check wheter the data is (first and second expression) not
> containing NULLS. Something like PRINT 'The Expression is --> ' +
> @Column1
>
> HTH, jens Suessmeyer.
>

Andy Thompson

2005-11-29, 11:23 am

try replacing it with

Column1 = CASE WHEN @Column1 IS NULL THEN
Column1
ELSE
@Column1
END

it may be longer, but really Coalesce and isnull are rarely needed, and
should only be used if there is no other option.

Andy

Sponsored Links