CODEWITHSUNDEEP
c#sqlsql-serverstored-proceduresssms
xyzzy
xyzzy

Reputation: 11

Is operator evaluation sequence in a SQL Server stored procedure impacted by the client making the call?

A stored procedure like the below consistently succeeds when called from SQL Server Management Studio (I am running 2012):

create procedure MyProc (@myString varchar(10))
as 
begin
    select 
        case 
           when isnumeric(@myString) = 1 AND cast(@myString AS Numeric) > 0
              then 'positive number'
           else 'negative number or non-numeric'
        end AS result
end

exec MyProc 'hello'

But if called from C#, the procedure consistently throws a conversion error, failing on the cast(@myString AS Numeric):

Msg 8114, Level 16, State 5, Procedure MyProc, Line 5
Error converting data type varchar to numeric.

It's as if the left-to-right sequential precedence described here (https://msdn.microsoft.com/en-us/library/ms190276.aspx) together with short circuiting behavior is not followed when the procedure is called from a C# program:

When two operators in an expression have the same operator precedence level, they are evaluated left to right based on their position in the expression

What would cause this difference in behavior? How might the client calling this stored procedure impact the sequence of AND operator evaluation?

Upvotes: 1

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

Use try_convert(). It is much more sensible:

create procedure MyProc (@myString varchar(10))
as 
begin
    select (case when try_convert(float, @myString) > 0
                 then 'positive number'
                 else 'negative number or non-numeric'
            end) AS result;
end;

exec MyProc 'hello';

I'm not sure why your version would fail. But, SQL Server does not guarantee the order of evaluation of boolean expressions.

I interpret the documentation that you site as being "logically evaluated". That is, it explains what the value of 4 - 2 + 27, but not the actual execution order in the engine. It doesn't make sense to me that SQL Server would specify evaluation order in this case, given all the other optimizations that it does, and the fact that ANSI doesn't require it.

Upvotes: 1

Related Questions