Monday, February 24, 2014

CASE Expression Issue in Linked Server Query

This blog is to explain a limitation with CASE ..WHEN construct when used in a linked server query which I recently came across.
So the scenario was this. I had a table in a remote SQL Server instance from which I wanted to retrieve some data from. For illustration let the table be named TestLS with columns as ID (identity column),Val (a varchar value column) and Cat ( a varchar category column)..
The requirement was to return a code instead of Category value by applying mapping rule in the query
So I formulated a query as below and executed it

Msg 8180, Level 16, State 1, Line 1Statement(s) could not be prepared.Msg 125, Level 15, State 4, Line 1Case expressions may only be nested to level 10.

I didn't understand why this was happening as I was sure I'm not using any nested CASE construct (as you can see from the above query). I discussed this with couple of my colleagues and they were also out of ideas.

So I went ahead and applied a workaround to get my requirement. I created a mapping table variable and populated it with values beforehand and then added a join to it get the code back. So modified suggestion looks like below

DECLARE @Mapping table

(

Cat varchar(30),Code int

)

insert @Mapping

SELECT 'Category 1' , 11 UNION ALL

SELECT 'Category 2' , 12 UNION ALL

SELECT 'Category 3' , 14 UNION ALL

SELECT 'Category 4' , 15 UNION ALL

SELECT 'Category 5' , 18 UNION ALL

SELECT 'Category 6' , 20 UNION ALL

SELECT 'Category 7' , 22 UNION ALL

SELECT 'Category 8' , 35 UNION ALL

SELECT 'Category 9' , 42 UNION ALL

SELECT 'Category 10' , 47 UNION ALL

SELECT 'Category 11' , 49 UNION ALL

SELECT 'Category 12' , 53 UNION ALL

SELECT 'Category 13' , 68 UNION ALL

SELECT 'Category 14' , 72 UNION ALL

SELECT 'Category 15' , 83 UNION ALL

SELECT 'Category 16' , 89 UNION ALL

SELECT 'Category 17' , 97 UNION ALL

SELECT 'Category 18' , 103 UNION ALL

SELECT 'Category 19' , 119 UNION ALL

SELECT 'Category 20' , 250

SELECT t.*,m.Code

FROM [LinkedServerName].VisakhTest.dbo.TestLS t

INNER JOIN @Mapping m

ON m.Cat = t.Cat

This solved my issue. But I still didn't understand the reason for my error.

So my curiosity prompted me to ask experts on this. I put this across to SQL Server experts and finally was able to get the reason, thanks to the idea provided by Erland.

I captured using SQL profiler the query passed to remote instance and got the below script

declare @p1 int

set @p1=NULL

exec sp_prepexec @p1 output,NULL,N'SELECT "Tbl1001"."ID" "Col1006","Tbl1001"."Val" "Col1007","Tbl1001"."Cat" "Col1008",CASE WHEN "Tbl1001"."Cat"=''Category 1'' THEN (11) ELSE CASE WHEN "Tbl1001"."Cat"=''Category 2'' THEN (12) ELSE CASE WHEN "Tbl1001"."Cat"=''Category 3'' THEN (14) ELSE CASE WHEN "Tbl1001"."Cat"=''Category 4'' THEN (15) ELSE CASE WHEN "Tbl1001"."Cat"=''Category 5'' THEN (18) ELSE CASE WHEN "Tbl1001"."Cat"=''Category 6'' THEN (20) ELSE CASE WHEN "Tbl1001"."Cat"=''Category 7'' THEN (22) ELSE CASE WHEN "Tbl1001"."Cat"=''Category 8'' THEN (35) ELSE CASE WHEN "Tbl1001"."Cat"=''Category 9'' THEN (42) ELSE CASE WHEN "Tbl1001"."Cat"=''Category 10'' THEN (47) ELSE CASE WHEN "Tbl1001"."Cat"=''Category 11'' THEN (49) ELSE CASE WHEN "Tbl1001"."Cat"=''Category 12'' THEN (53) ELSE CASE WHEN "Tbl1001"."Cat"=''Category 13'' THEN (68) ELSE CASE WHEN "Tbl1001"."Cat"=''Category 14'' THEN (72) ELSE CASE WHEN "Tbl1001"."Cat"=''Category 15'' THEN (83) ELSE CASE WHEN "Tbl1001"."Cat"=''Category 16'' THEN (89) ELSE CASE WHEN "Tbl1001"."Cat"=''Category 17'' THEN (97) ELSE CASE WHEN "Tbl1001"."Cat"=''Category 18'' THEN (103) ELSE CASE WHEN "Tbl1001"."Cat"=''Category 19'' THEN (119) ELSE CASE WHEN "Tbl1001"."Cat"=''Category 20'' THEN (250) ELSE NULL END END END END END END END END END END END END END END END END END END END END "Expr1002" FROM "VisakhTest"."dbo"."TestLS" "Tbl1001"'

select @p1

As you see from the above the linked server query is internally passed as a series of nested CASE...WHEN expressions to remote server which is why it throws the above error.

So keep in mind that whenever you want to write a similar CASE construct on a linked server query you need to keep conditional branches below or equal to 10. In cases where you've more branches, use mapping table approach as explained above or split CASE WHEN to multiple CASE constructs as below and merge using COALESCE function

SELECT *,

COALESCE(CASE

WHEN Cat='Category 1' THEN 11

WHEN Cat='Category 2' THEN 12

WHEN Cat='Category 3' THEN 14

WHEN Cat='Category 4' THEN 15

WHEN Cat='Category 5' THEN 18

WHEN Cat='Category 6' THEN 20

WHEN Cat='Category 7' THEN 22

WHEN Cat='Category 8' THEN 35

ELSE NULL

END,

CASE

WHEN Cat='Category 9' THEN 42

WHEN Cat='Category 10' THEN 47

WHEN Cat='Category 11' THEN 49

WHEN Cat='Category 12' THEN 53

WHEN Cat='Category 13' THEN 68

WHEN Cat='Category 14' THEN 72

WHEN Cat='Category 15' THEN 83

WHEN Cat='Category 16' THEN 89

ELSE NULL

END

,

CASE

WHEN Cat='Category 17' THEN 97

WHEN Cat='Category 18' THEN 103

WHEN Cat='Category 19' THEN 119

WHEN Cat='Category 20' THEN 250

END) AS CatCode

FROM [LinkedServerName].VisakhTest.dbo.TestLS t

I would prefer the earlier explained mapping table method as it has better clarity and is far more intuitive