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!
trs28Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
It may not be an issue with the comparison logic. A bit variable can only have a value of 0, 1, or NULL. Your calling code may be treating the 2 as one of those values...most likely 1.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pawan KumarDatabase ExpertCommented:
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

0
Kelvin SparksCommented:
@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
0
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Pawan KumarDatabase ExpertCommented:
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

0
Pawan KumarDatabase ExpertCommented:
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

0
Pawan KumarDatabase ExpertCommented:
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

0
Pawan KumarDatabase ExpertCommented:
Pls wait.
0
Pawan KumarDatabase ExpertCommented:
Okay I got why it is not working.

The problem in when you are passing 2 or any number greater than 1 Bit field will contain 1 only. That's why that error is not coming..

Script to test the above case

CREATE TABLE Bits
(
	ID BIT
)
GO

INSERT INTO Bits VALUES (100)

INSERT INTO Bits VALUES (0)

INSERT INTO Bits VALUES (1)

SELECT * FROM Bits

Open in new window


Now change the bit field to tinyint, updated code for you !!

ALTER PROCEDURE [dbo].[sp_ControlVariableCC]
      @Enable TINYINT = 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


Enjoy !! Good learning..
0
trs28Author Commented:
Thanks everyone for the assistance!   I knew I was doing something stupid and staring me right in the face the whole time!
0
trs28Author Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.