Symptom
- if @a is null will be raised Error 403 if @a is a text tyep.
1> declare @a text
2> set @a=null
3> if @a is null
4> select 1
5> else
6> select -1
7> go
Msg 403, Level 16, State 2:
Server 'BK9', Line 3:
Invalid operator for datatype op: is null type: TEXT. - But when enabling "streamlined dynamic SQL", it's not raised Error 403 and return unexpected behavior when assigning a return value of case when.
1.if is null
1> declare @a text
2> set @a=null
3> if @a is null
4> select 1
5> else
6> select -1
7> go
(1 row affected)-----------
1(1 row affected)
1>
2.case when : refer to "Reproducing the Issue" for detail repro steps
1> declare @t1 text, @t2 text, @t text
2> select @t = case when t2.id is null then t1.t1 else t2.t2 end, @t1=t1.t1,@t2=t2.t2
3> from #table1 t1
4> left outer join #table2 t2 on t2.id = t1.id
5>
6> if @t1 is null
7> select '@t1 is null'
8> else
9> select '@t1 is not null'
10>
11>
12> if @t2 is null
13> select '@t2 is null'
14> else
15> select '@t2 is not null'
16>
17> if @t is null
18> select '@t is null'
19> else
20> select '@t is not null'
21>
22> go
(1 row affected)-----------
@t1 is null(1 row affected)
-----------
@t2 is null(1 row affected)
--------------
@t is not null <<<wrong return value(1 row affected)
1>
Read more...
Environment
SAP Adaptive Server Enterprise (ASE)
Product
Keywords
text datatype, case when, unexpected behavior, Msg 403, CR830181, CR#830181, 830181 , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Problem
About this page
This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required).Search for additional results
Visit SAP Support Portal's SAP Notes and KBA Search.