Why does this CASE
expression:
SELECT CASE column
WHEN 'a' THEN '1'
WHEN 'b' THEN '2'
... c -> i
WHEN 'j' THEN '10'
WHEN 'k' THEN '11'
END [col]
FROM LinkedServer.database.dbo.table
Produce this result?
Error message: 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.
Clearly there isn't a nested CASE
expression here, though there are more than 10 "branches."
Another oddity. This inline table-valued function produces the same error:
ALTER FUNCTION [dbo].[fn_MyFunction]
(
@var varchar(20)
)
RETURNS TABLE
AS
RETURN
(
SELECT CASE column
WHEN 'a' THEN '1'
WHEN 'b' THEN '2'
... c -> i
WHEN 'j' THEN '10'
WHEN 'k' THEN '11'
END [col]
FROM LinkedServer.database.dbo.table
)
But a similar multi-statement TVF works fine:
ALTER FUNCTION [dbo].[fn_MyFunction]
(
@var varchar(20)
)
RETURNS @result TABLE
(
value varchar(max)
)
AS
BEGIN
INSERT INTO @result
SELECT CASE column
WHEN 'a' THEN '1'
WHEN 'b' THEN '2'
... c -> i
WHEN 'j' THEN '10'
WHEN 'k' THEN '11'
END [col]
FROM LinkedServer.database.dbo.table
RETURN;
END
Best Answer
Not in the query text, no. But the parser always expands
CASE
expressions to the nested form:That query is local (no linked server) and the Compute Scalar defines the following expression:
This is fine when executed locally, because the parser does not see a nested
CASE
statement over 10 levels deep (though it does pass one on to the later stages of local query compilation).However, with a linked server, the generated text may be sent to the remote server for compilation. If that is the case, the remote parser sees a nested
CASE
statement more than 10 levels deep and you get error 8180.The in-line function is expanded in-place into the original query text, so it's no surprise the same error results with the linked server.
Similar, but not the same. The msTVF involves an implicit conversion to
varchar(max)
, which happens to prevent theCASE
expression being sent to the remote server. Because theCASE
is evaluated locally, a parser never sees an over-nestedCASE
and there is no error. If you change the table definition fromvarchar(max)
to the implicit type of theCASE
result -varchar(2)
- the expression is remoted with the msTVF and you will get an error.Ultimately, the error occurs when an over-nested
CASE
is evaluated by the remote server. If theCASE
is not evaluated in the Remote Query iterator, no error results. For example, the following includes aCONVERT
that is not remoted, so no error occurs even though a linked server is used: