Background
I experienced an error trying to run a SQL Statement that I wrote a few months ago.
Error Message
Error Message – “Msg 102, Level 15, State 1, Line “
The error reads:-
Image
Textual
Msg 102, Level 15, State 1, Line 28 Incorrect syntax near '='.
What is IIF?
Microsoft – SQL Server – Transact SQL
IIF is a shorthand way for writing a CASE expression. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation. That is, the true_value is returned if the Boolean expression is true, and the false_value is returned if the Boolean expression is false or unknown. true_value and false_value can be of any type. The same rules that apply to the CASE expression for Boolean expressions, null handling, and return types also apply to IIF.
Code
Basic Example
SQL
Here is the SQL Code:-
declare @a char(1)
declare @b char(1)
set @a = 'a';
set @b = 'b';
select [@@version] = @@version
select
[@a] = @a
, [@b] = @b
, [is_a_equal_b_using_Case]
= case
when ( @a = @b )
then 'Yes'
else 'No'
end
, [is_a_equal_b_using_IIF]
= IIF
(
@a = @b
, 'Yes'
, 'No'
)
Output
Grid
Explanation
Case Statement
Code Snippet
case
when ( @a = @b )
then 'Yes'
else 'No'
end
Elaborate
- If @a = @b
- Then ‘Yes’
- else
- ‘No’
IIF Conditonal
Code
IIF
(
@a = @b
, 'Yes'
, 'No'
)
Elaborate
- If @a = @b
- Then ‘Yes’
- else
- ‘No’
Scenario – 2 – At least one of the result expressions in a CASE specification must be an expression other than the NULL constant ( Msg 8133 )
Outline
If all result expressions yield a null constant, we will get an error.
The error will read:-
Msg 8133, Level 16, State 1, Line 1
At least one of the result expressions in a CASE specification must be an expression other than the NULL constant.
At least one of the result expressions in a CASE specification must be an expression other than the NULL constant.
SQL
Here is the SQL Code:-
select
[Result]
= IIF
(
1 = 2
, NULL
, NULL
);
select
[result]
= case
when 1 = 2
then null
else null
end
Output
Image
Text
Msg 8133, Level 16, State 1, Line 1 At least one of the result expressions in a CASE specification must be an expression other than the NULL constant. Msg 8133, Level 16, State 1, Line 11 At least one of the result expressions in a CASE specification must be an expression other than the NULL constant.
Explanation
When is the error generated.
The error is generated during SQL Parsing.
That is the SQL Code block is not executed, at all.
Scenario – 3 – Transact SQL Parser Error ( Msg 102 )
Outline
If one is running a SQL Server Version preceeding SQL Server 2012, we will encounter an error.
The error will read:-
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ‘=’.
SQL
Here is the SQL Code:-
set nocount on
go
print 'version'
print '-------'
go
print cast(@@version as varchar(600));
go
SELECT
[Result]
= IIF
(
1 = 2
, 'IIF.Yes'
, 'IIF.No'
);
select
[result]
= case
when 1 = 2
then 'Case.Yes'
else 'Case.No'
end
Output
SQL Server Version 2008 R2
Image – SQL Server Version 2008 R2
Text – SQL Server Version 2008 R2
version ------- Microsoft SQL Server 2008 R2 (SP3-GDR) (KB4057113) - 10.50.6560.0 (X64) Dec 28 2017 15:03:48 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)anguage="text"]
Error Message – 102
Image – Msg 102
Text – Msg 102
Msg 102, Level 15, State 1, Line 16 Incorrect syntax near '='.
Explanation
- IIF was introduced in Microsoft SQL Server v2012.
- Prior to v2012, the parser will run into a ditch upon encountering the IIF keyword
Learning in the Open 