CODEWITHSUNDEEP
sqlsybasesap-aseisnull
Prateek Gupta
Prateek Gupta

Reputation: 109

Selecting with quotes with ISNULL

I am trying to build a dynamic query in sybase like: 

SELECT @v_query = @v_query + " tech= "+ ISNULL(''''+@stat+'''',"tech")

When I pass @stat as NULL, I get '' as output but expected output is tech which is wrong

If I pass@stat as NACK, it should return 'NACK' which works as expected

Thanks for any suggestions on how to achieve this

Upvotes: 1

Views: 153

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

To be honest, you probably want this:

SELECT @v_query = @v_query + 
                  (CASE WHEN @stat IS NULL THEN '1=1'
                        ELSE 'tech = ''' + @stat + ''''
                   END)

Don't use tech = tech as a no-op (it doesn't work for NULLs). Instead, just remove the comparison.

Upvotes: 1

Moudiz
Moudiz

Reputation: 7377

remove the '' and retry

declare @SQL_TXT varchar(100)
declare @stat varchar(100)
begin
select @stat= null
SELECT @SQL_TXT=@SQL_TXT + ' tech =' + ISNULL(@stat,'tech')
print @SQL_TXT
end
print tech =tech

edit you can do it like this 

declare @SQL_TXT varchar(100)
declare @stat varchar(100)
begin
select @stat= null
SELECT @SQL_TXT=@SQL_TXT + ' tech = ''' + ISNULL(@stat,'tech') +''''
print @SQL_TXT
end

it seems adding quotes in null function give null value , can you add quotes originally to the variable as alternative ?

declare @SQL_TXT varchar(100)
declare @stat varchar(100)
begin
select @stat= 'SAMPLE'

select @stat= ''''+@stat+''''
SELECT @SQL_TXT=@SQL_TXT + ' tech = ' + ISNULL(@stat,'tech') 
print @SQL_TXT
end

Upvotes: 0

Related Questions