T-SQL Return text in CASE with numeric check

When I do this :

select 
    'Result' = 
    case 
        when my_table.value > 0 
        then 'Correct'
        else 'Not Correct'
    end
from table as my_table

It always returns something like this:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Correct' to data type int.

How can I achieve this ? Isn't it possible to return a text value after a numeric check in a CASE statement ?

thanks !

k.

Answers


There is nothing wrong technically wrong with your syntax, your query can be fairly easily replicated with:

SELECT 'Result' = CASE WHEN my_table.value > 0  THEN 'Correct'
                        ELSE 'Not Correct'
                    END
FROM (VALUES (1), (0)) My_Table (Value);

which works fine. You can do whatever checks you like as long as all the return types are the same, or implicitly convertable to the data type with the highest precedence, e.g. This still works:

SELECT 'Result' = CASE WHEN my_table.value > 0  THEN 1 -- INTEGER/INTEGER
                        WHEN 'Test' = 'Test2' THEN 0.5 -- TEXT/DECIMAL 
                        ELSE '0' --/TEXT
                    END
FROM (VALUES (1), (0)) My_Table (Value);

The comment shows the Expresson Type/Return Type. I suspect that the actual problem comes from either a UNION that you have not posted, or further expressions in the case statement that you have not posted.

For example, although the top part runs fine on its own above, running this:

SELECT 'Result' = CASE WHEN my_table.value > 0  THEN 'Correct'
                        ELSE 'Not Correct'
                    END
FROM (VALUES (1), (0)) My_Table (Value)
UNION ALL
SELECT 1;

Will give the following error:

Conversion failed when converting the varchar value 'Correct' to data type int.

As an aside, I'd suggest getting away from using literals for column aliases though, for one it is on the deprecation list, but IMO it can also get confusing, especially, as in this case, when combined with actual string literals.


Need Your Help

std::function bound to member function

c++ visual-c++ c++11 visual-c++-2012

The following code doesn't compile in VS2012

How to manage assets for modern/ancient browsers

css http browser

With new browser enhancements such as CSS3 animations, gradients, SVG backgrounds, etc... it becomes unnecessary for newer browsers to have to download legacy CSS code and for legacy browsers to ha...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.