Null problem

Hi all ,
I am using MicrosoftSQL 2000 server in Window 2000 Pro.
I have problem update column "SEC_CARRIER" which allow NULL values .
When execute sql below , it did not update SEC_CARRIER column at all .
However if i added some value in the SEC_CARRIER manually then execute
sql below , is ok .
Anyone have any ideas how to solve this problem ? I cannot change the
column to NOT NULL values because it have implemented and live.
UPDATE ILS_USER_PROFILE
SET CARRIER_NO = @.carrier_no ,
SEC_CARRIER = CARRIER_NO + '|' + SEC_CARRIER,
DATE_CARD_ISSUE = @.date_card_issue,
CARD_LOST = @.card_lost
WHERE CARD_LOST ='N'
AND BARCODE = @.barcode
ThanksHi
NULL concatenated with a valid string is still NULL.
UPDATE ILS_USER_PROFILE
SET CARRIER_NO = @.carrier_no ,
SEC_CARRIER = CARRIER_NO + '|' + ISNULL(SEC_CARRIER, ''),
DATE_CARD_ISSUE = @.date_card_issue,
CARD_LOST = @.card_lost
WHERE CARD_LOST ='N'
AND BARCODE = @.barcode
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"weetat" <test@.wavex-tech.com> wrote in message
news:OjkIp6c5FHA.472@.TK2MSFTNGP15.phx.gbl...
> Hi all ,
> I am using MicrosoftSQL 2000 server in Window 2000 Pro.
> I have problem update column "SEC_CARRIER" which allow NULL values .
> When execute sql below , it did not update SEC_CARRIER column at all .
> However if i added some value in the SEC_CARRIER manually then execute
> sql below , is ok .
> Anyone have any ideas how to solve this problem ? I cannot change the
> column to NOT NULL values because it have implemented and live.
> UPDATE ILS_USER_PROFILE
> SET CARRIER_NO = @.carrier_no ,
> SEC_CARRIER = CARRIER_NO + '|' + SEC_CARRIER,
> DATE_CARD_ISSUE = @.date_card_issue,
> CARD_LOST = @.card_lost
> WHERE CARD_LOST ='N'
> AND BARCODE = @.barcode
> Thanks|||Thanks , the solve it.
However , how to remove "<NULL>" value in the column ? below is my
stored procedure sql :
example : 12357869|90909|<NULL> --> remove <NULL>
CREATE PROCEDURE dbo.update_carrier
@.barcode varchar(14),
@.carrier_no varchar(200),
@.first_activated datetime,
@.card_lost varchar(1)
AS
UPDATE ILS_USER_PROFILE
SET CARRIER_NO = @.carrier_no ,
FIRST_ACTIVATED = @.first_activated,
CARD_LOST = @.card_lost
WHERE CARD_LOST ='N'
AND BARCODE = @.barcode
GO
Thanks
Mike Epprecht (SQL MVP) wrote:
> Hi
> NULL concatenated with a valid string is still NULL.
> UPDATE ILS_USER_PROFILE
> SET CARRIER_NO = @.carrier_no ,
> SEC_CARRIER = CARRIER_NO + '|' + ISNULL(SEC_CARRIER, '')
,
> DATE_CARD_ISSUE = @.date_card_issue,
> CARD_LOST = @.card_lost
> WHERE CARD_LOST ='N'
> AND BARCODE = @.barcode
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "weetat" <test@.wavex-tech.com> wrote in message
> news:OjkIp6c5FHA.472@.TK2MSFTNGP15.phx.gbl...
>
>
>