Tweeted twitter.com/#!/StackDBAs/status/338431200815378432
5 Just made it a bit shorter by shorthanding the expression
source | link

Why do linked servers have a limitation of 10 itemsbranches in a CASE expression?

Why does this CASE expression:

SELECT CASE column 
        WHEN 'a' THEN '1' 
        WHEN 'b' THEN '2' 
        WHEN 'c' THEN '3' 
        WHEN 'd' THEN '4' 
        WHEN 'e' THEN '5' 
        WHEN 'f' THEN '6' 
        WHEN 'g' THEN '7' 
        WHEN 'h' THEN '8' 
        WHEN 'i'... THENc '9'-> i
        WHEN 'j' THEN '10' 
        WHEN 'k' THEN '11'  
    END [col] 
FROM LinkedServer.database.dbo.table

ProducesProduce 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."

ALTER FUNCTION [dbo].[fn_MyFunction]
(   
     @var varchar(20)
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT CASE column 
            WHEN 'a' THEN '1' 
            WHEN 'b' THEN '2' 
            WHEN 'c' THEN '3' 
            WHEN 'd' THEN '4' 
            WHEN 'e' THEN '5' 
            WHEN 'f' THEN '6' 
            WHEN 'g' THEN '7' 
            WHEN 'h' THEN '8' 
            WHEN 'i'... THENc '9'-> i
            WHEN 'j' THEN '10' 
            WHEN 'k' THEN '11'  
        END [col] 
    FROM LinkedServer.database.dbo.table
)
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' 
            WHEN 'c' THEN '3' 
            WHEN 'd' THEN '4' 
            WHEN 'e' THEN '5' 
            WHEN 'f' THEN '6' 
            WHEN 'g' THEN '7' 
            WHEN 'h' THEN '8' 
            WHEN 'i'... THENc '9'-> i
            WHEN 'j' THEN '10' 
            WHEN 'k' THEN '11'  
        END [col] 
    FROM LinkedServer.database.dbo.table

RETURN;
END

Why do linked servers have a limitation of 10 items in a CASE expression?

Why does this expression:

SELECT CASE column 
        WHEN 'a' THEN '1' 
        WHEN 'b' THEN '2' 
        WHEN 'c' THEN '3' 
        WHEN 'd' THEN '4' 
        WHEN 'e' THEN '5' 
        WHEN 'f' THEN '6' 
        WHEN 'g' THEN '7' 
        WHEN 'h' THEN '8' 
        WHEN 'i' THEN '9' 
        WHEN 'j' THEN '10' 
        WHEN 'k' THEN '11'  
    END [col] 
FROM LinkedServer.database.dbo.table

Produces 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.

ALTER FUNCTION [dbo].[fn_MyFunction]
(   
     @var varchar(20)
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT CASE column 
            WHEN 'a' THEN '1' 
            WHEN 'b' THEN '2' 
            WHEN 'c' THEN '3' 
            WHEN 'd' THEN '4' 
            WHEN 'e' THEN '5' 
            WHEN 'f' THEN '6' 
            WHEN 'g' THEN '7' 
            WHEN 'h' THEN '8' 
            WHEN 'i' THEN '9' 
            WHEN 'j' THEN '10' 
            WHEN 'k' THEN '11'  
        END [col] 
    FROM LinkedServer.database.dbo.table
)
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' 
            WHEN 'c' THEN '3' 
            WHEN 'd' THEN '4' 
            WHEN 'e' THEN '5' 
            WHEN 'f' THEN '6' 
            WHEN 'g' THEN '7' 
            WHEN 'h' THEN '8' 
            WHEN 'i' THEN '9' 
            WHEN 'j' THEN '10' 
            WHEN 'k' THEN '11'  
        END [col] 
    FROM LinkedServer.database.dbo.table

RETURN;
END

Why do linked servers have a limitation of 10 branches in a CASE expression?

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."

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
)
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
4 edited tags
| link
3 added 7 characters in body; edited title
source | link

Why case expressions usingdo linked servers cannot have more thana limitation of 10 items in thema CASE expression?

Why does this expression:

produces the nextProduces 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.

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.*

And whereClearly there isisn't a nested caseCASE expression?

====================== UPDATE ======================= here.

Another oddity.

  This inline table-valued function produces mentionedthe same error:

but thisBut a similar multi-statement TVF works fine:

Why case expressions using linked servers cannot have more than 10 items in them?

Why this expression:

produces the next 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.

And where there is nested case expression?

====================== UPDATE =======================

Another oddity.

  This function produces mentioned error

but this works fine

Why do linked servers have a limitation of 10 items in a CASE expression?

Why does this expression:

Produces 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.

Another oddity. This inline table-valued function produces the same error:

But a similar multi-statement TVF works fine:

2 added 1287 characters in body
source | link
1
source | link