Link to home
Create AccountLog in
Avatar of trs28
trs28

asked on

Simple IF evaluation mistake in SQL Azure statement

I'm doing something stupid and not evaluating a stored procedure parameter properly in an IF block.    (SQL Azure DB)

Here is the SP ....

ALTER PROCEDURE [dbo].[sp_ControlVariableCC]
      @Enable bit = NULL
AS
BEGIN
        DECLARE @Enable_Msg varchar(max) = 'error message text';

        IF (@Enable <> 0 AND @Enable <> 1) OR @Enable IS NULL
      BEGIN
            THROW 90002, @Enable_Msg, 1
      END;

This is just fluff SQL to ensure that the user doesn't enter something other than what they're supposed to and to provide a more detailed error message to the user than the system would generate.    If I don't enter in the parameter when calling the SP, it throws properly.   However, if I enter 2 for the parameter, it does not throw as expected.   I know I'm doing something extremely stupid here w/ the comparison logic.   Any help is very much appreciated!
ASKER CERTIFIED SOLUTION
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Try this..This will work.

ALTER PROCEDURE [dbo].[sp_ControlVariableCC]
      @Enable bit = NULL
AS 
BEGIN
        DECLARE @Enable_Msg VARCHAR(100) = 'error message text'

        IF (@Enable <> 0 AND @Enable <> 1) OR @Enable IS NULL      
            THROW 90002, @Enable_Msg, 1
      
END

Open in new window

@Enable is a bit datatype. Valid options are 0 and 1. What tends to happen when passing integers is that 0 is treated as 0 and any other value becomes 1 (or true). The more correct way to evaluate but datatypes would be ID @Enable or NOT @Enable (i.e. IF (NOT @Enable AND @Enable )  but I suspect the same occurs.

I think that sort of checking needs to happen prior to passing the value to the SP.


Kelvin
may be

ALTER PROCEDURE [dbo].[sp_ControlVariableCC]
      @Enable bit = NULL
AS 
BEGIN
        DECLARE @Enable_Msg VARCHAR(100) = 'error message text'

            IF @Enable IS NULL      
            THROW 90002, @Enable_Msg, 1
		ELSE IF @Enable != 1
			THROW 90002, @Enable_Msg, 1
		ELSE IF @Enable != 0
			THROW 90002, @Enable_Msg, 1
END

Open in new window

got it AND is the issue. try this

ALTER PROCEDURE [dbo].[sp_ControlVariableCC]
      @Enable bit = NULL
AS 
BEGIN
        DECLARE @Enable_Msg VARCHAR(100) = 'error message text'

        IF @Enable <> 0 OR @Enable <> 1 OR @Enable IS NULL      
            THROW 90002, @Enable_Msg, 1
      
END

Open in new window

tested one...

ALTER PROCEDURE [dbo].[sp_ControlVariableCC]
      @Enable bit = NULL
AS 
BEGIN
        DECLARE @Enable_Msg VARCHAR(100) = 'error message text'

            IF @Enable IS NULL      
            THROW 90002, @Enable_Msg, 1
		ELSE IF @Enable != 1
			THROW 90002, @Enable_Msg, 1
		ELSE IF @Enable != 0
			THROW 90002, @Enable_Msg, 1
END

Open in new window

Pls wait.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of trs28
trs28

ASKER

Thanks everyone for the assistance!   I knew I was doing something stupid and staring me right in the face the whole time!
Avatar of trs28

ASKER

Pawan Kumar Khowal put in a little bit of time and effort into working this out.  Even though Shaun Kline was first and completely accurate, I had to reward the effort even after it was already figured out.