asked on

ISNULL value replacement , withing a string

Hi all
I looking for a way to replace a column
if the value is NULL with an alternate value

Since I need to assign the select statement to a variable..
The current formating I got, is not working see code below

declare 

@ErrorMessage varchar(25)
set @ErrorMessage = 'This is a  2248 Error'

SET @sql_trans_VProtokoll	 = ' SELECT i.EBILL as Transaction,SUBSTRING(h.zeichnung,1,15) as Zeichnung,
ISNULL(i.infoText, ''' + @ErrorMessage + ''') as ReasonText '

Open in new window

Rajkumar Gs

Check this modified query

declare @sql_trans_VProtokoll varchar(max)
declare @ErrorMessage varchar(25)
set @ErrorMessage = 'This is a  2248 Error'

SET @sql_trans_VProtokoll	 = ' SELECT i.EBILL as Transaction,SUBSTRING(h.zeichnung,1,15) as Zeichnung,
ISNULL(i.infoText, ''' + @ErrorMessage + ''') as ReasonText '

-- check the value
select @sql_trans_VProtokoll

Open in new window

Lee

What error are you getting?

ZURINET

ASKER

Hallo RajkumarGS

What is the difference between my code and yours?
My problem is that the msg @ErrorMessage = 'This is a 2248 Error'
is not returned..
The query is returning -- null

Lee

How have you defined @sql_trans_VProtokoll?

qasim_md

Here is the solution

declare @ErrorMessage varchar(25)
declare @sql_trans_VProtokoll varchar(max)
set @ErrorMessage = 'This is a 2248 Error'

SET @sql_trans_VProtokoll = ' SELECT i.EBILL as ''Transaction'',SUBSTRING(h.zeichnung,1,15) as Zeichnung,
ISNULL(i.infoText, ''' + @ErrorMessage + ''') as ReasonText '

print (@sql_trans_VProtokoll)
--exec (@sql_trans_VProtokoll)

ZURINET

ASKER

Find where the problem lies...
The infoText is declared as sql data type text
and the value inside is NULL string.. and not null

Wondering how I can solve this now?

ASKER CERTIFIED SOLUTION

qasim_md

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ZURINET

ASKER

The problem lies in the data I have in the database