Update Table? - 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

SQL Server Development (2000)

Update Table?

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-08-04 : 06:33:41

With this procudure, cannot update if existing cmt is "NULL",May i know what's wrong?
CREATE PROCEDURE [dbo].[upt]
@id int,@cmt nvarchar(500),@dc datetime
AS
update tblRenewalList
set cmt=cmt + @cmt,DateClosed=@dc
where AutoID=@id
GO

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-04 : 06:38:17

adding anything to a null means the whole string will become null. look at coalesce or isnull in books online

Em

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-08-04 : 06:42:51

Plz can u suggest me how can i modify it?

quote:
Originally posted by elancaster

adding anything to a null means the whole string will become null. look at coalesce or isnull in books online

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-04 : 06:44:34

did you look up coalesce and try and figure it out?

Em

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-08-04 : 06:48:44

I tried,but i'm confusing..

quote:
Originally posted by elancaster

did you look up coalesce and try and figure it out?

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-04 : 06:51:46

show us what you tried then

Em

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-08-04 : 06:57:08

Actually, I'm new in sql
I'm afraid to show my statement coz incorrect syntax
But i'll show u

CREATE PROCEDURE [dbo].[d_Update]
@id int,@cmt nvarchar(500),@dc datetime
AS
update Table
if not isnull(Cmt) then
set Cmt=Cmt + @cmt,DateClosed=@dc
else
set Cmt=@cmt,DateClosed=@dc
end if
where AutoID=@id
GO

quote:
Originally posted by elancaster

show us what you tried then

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-04 : 07:00:01

from BOL...

quote:

Syntax
ISNULL ( check_expression , replacement_value )

Arguments
check_expression
Is the expression to be checked for NULL. check_expression can be of any type.

replacement_value
Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expresssion.

...so in your case the check_expression could just be cmt ? and the replacement_value could just be an empty string?

so...
update tblRenewalList
set cmt = isnull(cmt,'') + @cmt,DateClosed=@dc
where AutoID=@id

Em

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-08-04 : 07:08:43

Thank u

quote:
Originally posted by elancaster

from BOL...

quote:

Syntax
ISNULL ( check_expression , replacement_value )

Arguments
check_expression
Is the expression to be checked for NULL. check_expression can be of any type.

replacement_value
Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expresssion.

...so in your case the check_expression could just be cmt ? and the replacement_value could just be an empty string?

so...
update tblRenewalList
set cmt = isnull(cmt,'') + @cmt,DateClosed=@dc
where AutoID=@id

Em

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources