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
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
SELECT *,
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
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
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
To my surprise, this was the result I got
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 125, Level 15, State 4, Line 1
Case 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
