Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
@listik I tried - did not help –  Andrey May 22 '13 at 20:47
    
MSDN specifies this, but gives no explanation: msdn.microsoft.com/en-us/library/ms181765.aspx "Remarks SQL Server allows for only 10 levels of nesting in CASE expressions." –  ooutwire May 22 '13 at 20:48
    
@ooutwire - there are no NESTED case statements :) Example of nested statement CASE field WHEN 'a' THEN '1' ELSE CASE THEN 'b' THEN '2' ELSE CASE ... END END –  Andrey May 22 '13 at 20:51
    
@AaronBertrand linked server type: SQL Server, on my side SQL Server 2008 on the other side SQL Server 2005 –  Andrey May 22 '13 at 20:54
    
@Andrey But can you prove that SQL Server does not see your query as nested? I think we have a prima facie case, due to the 10 level limit, that SQL Server sees this simple case statement as nested. –  ooutwire May 22 '13 at 21:32

3 Answers 3

up vote 14 down vote accepted

Clearly there isn't a nested CASE expression here.

Not in the query text, no. But the parser always expands CASE expressions to the nested form:

SELECT CASE SUBSTRING(p.Name, 1, 1)
        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
FROM AdventureWorks2012.Production.Product AS p

Local query plan

That query is local (no linked server) and the Compute Scalar defines the following expression:

Nested CASE 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.

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

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.

But a similar multi-statement TVF works fine

Similar, but not the same. The msTVF involves an implicit conversion to varchar(max), which happens to prevent the CASE expression being sent to the remote server. Because the CASE is evaluated locally, a parser never sees an over-nested CASE and there is no error. If you change the table definition from varchar(max) to the implicit type of the CASE 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 the CASE is not evaluated in the Remote Query iterator, no error results. For example, the following includes a CONVERT that is not remoted, so no error occurs even though a linked server is used:

SELECT CASE CONVERT(varchar(max), SUBSTRING(p.Name, 1, 1))
        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
FROM SQL2K8R2.AdventureWorks.Production.Product AS p

CASE not remoted

share|improve this answer
    
great explanation - thanks! –  Andrey May 23 '13 at 5:28

My hunch is that the query is getting re-written somewhere along the way to have a slightly different CASE structure, e.g.

CASE WHEN column = 'a' THEN '1' ELSE CASE WHEN column = 'b' THEN '2' ELSE ...

I believe this is a bug in whatever linked server provider you're using (in fact perhaps all of them - I've seen it reported against several). I also believe you shouldn't hold your breath waiting for a fix, either in the functionality or the confusing error message explaining the behavior - this has been reported for a long time, involves linked servers (which haven't had much love since SQL Server 2000), and impacts far fewer people than this confusing error message, which has yet to be fixed after the same longevity.

As Paul points out, SQL Server is expanding your CASE expression to the nested variety, and the linked server doesn't like it. The error message is confusing, but only because the underlying conversion of the expression is not immediately visible (nor intuitive in any way).

One workaround (other than the function change you added to your question) would be to create a view or stored procedure on the linked server, and reference that, instead of passing the full query through the linked server provider.

Another (assuming your query really is this simplistic, and you just want the numeric coefficient of letters a-z) is to have:

SELECT [col] = RTRIM(ASCII([column])-96)
FROM LinkedServer.database.dbo.table;

If you absolutely need this to work as is, I suggest you contact support directly and open a case, though I can't vouch for the results - they may just provide you with workarounds you already have access to on this page.

share|improve this answer
    
This is a good supposition - but I have a solution, I just wonder why that is so confusing error message :) –  Andrey May 22 '13 at 21:01
    
@Andrey because linked servers really haven't had much attention paid to them since SQL Server 2000, and I'm sure it's enough busy work for the SQL team just to keep linked servers working with all the functionality they've added. They don't seem to care much about confusing error messages. –  Aaron Bertrand May 22 '13 at 21:05
    
seems to be true, but I'll wait maybe someone else will offer diff answer. By your answer it turns out that this is a bug –  Andrey May 22 '13 at 21:19
    
@Andrey don't expect this bug to be fixed. If you search around you'll find that this has been a known problem for many, many years. –  Aaron Bertrand May 22 '13 at 21:20
    
But that does not mean we should not ask questions :) –  Andrey May 22 '13 at 21:22

you can get around this by

SELECT COALESCE(
CASE SUBSTRING(p.Name, 1, 1)
    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' 
    ELSE NULL
END,
CASE SUBSTRING(p.Name, 1, 1)
    WHEN 'j' THEN '10' 
    WHEN 'k' THEN '11'  
END)
FROM SQL2K8R2.AdventureWorks.Production.Product AS p
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.