Why else part is executed even the first case when condition is true in SQL Server?

8aqjt8rx 于 8个月前发布在 SQL Server

关注(0)|答案(1)|浏览(63)

SELECT
    CASE                      
       WHEN CHARINDEX('$','$2,500') > 0              
           THEN
               CASE 
                   WHEN CHARINDEX(' ','$2,500', CHARINDEX('$','$2,500') + 2) > 0  
                       THEN SUBSTRING('$2,500', CHARINDEX('$', '$2,500'), CHARINDEX(' ', '$2,500', CHARINDEX('$', '$2,500') + 2) - CHARINDEX('$', '$2,500'))              
                       ELSE SUBSTRING('$2,500', CHARINDEX('$', '$2,500'), LEN('$2,500') - CHARINDEX('$', '$2,500') + 1)           
               END                
           ELSE '$2,500'                      
    END

I have a variable of type varchar which may contain some random words after the number so what I am doing is if the variable contains space after the number (space may also be there after $ so we do not have to consider that) then take substring from the $ sign till the space after the number.

But in the above case, there is no space so it should go into if part which is basically going but it is giving the invalid length parameter passed error.

SELECT
    CASE                      
        WHEN CHARINDEX('$', '$2,500') > 0              
            THEN
                CASE 
                    WHEN CHARINDEX(' ', '$2,500', CHARINDEX('$', '$2,500') + 2) > 0  
                        THEN SUBSTRING('$2,500', CHARINDEX('$', '$2,500'), CHARINDEX(' ', '$2,500', CHARINDEX('$', '$2,500') + 2) - CHARINDEX('$', '$2,500'))              
                        ELSE SUBSTRING('$2,500', CHARINDEX('$', '$2,500'), LEN('$2,500') - CHARINDEX('$', '$2,500') + 1) 
                END                
            ELSE '$2,500'                      
    END

sql-server

来源：https://stackoverflow.com/questions/77352030/why-else-part-is-executed-even-the-first-case-when-condition-is-true-in-sql-serv

1条答案

按热度按时间

mv1qrgav1#

You are passing a bad parameter into the substring function, even though that function doesn't end up in your execution path. It still must meet the requirements for the function.

From the documentation (link above), the length parameter "Is a positive integer or bigint expression that specifies how many characters of the expression will be returned. If length is negative, an error is generated and the statement is terminated."

You can see the issue here:

SELECT CASE WHEN 1=0 THEN SUBSTRING('$2,500',1,-5) ELSE 'Success' END

Invalid length parameter passed to the substring function.

This throws the same error, even though the WHEN path of the case expression is not viable.

Because you can't pass a negative value to the length parameter of the substring() function, you will need to account for this in your sql using something like the following:

THEN SUBSTRING('$2,500', CHARINDEX('$', '$2,500'), GREATEST(CHARINDEX(' ', '$2,500', CHARINDEX('$', '$2,500') + 2) - CHARINDEX('$', '$2,500'), 0))

Working dbfiddle here

赞(0）回复(0）举报 8个月前

我来回答

Why else part is executed even the first case when condition is true in SQL Server?

1条答案

相关问题

热门标签

最新问答