Solved

Simple IF evaluation mistake in SQL Azure statement

Posted on 2016-09-27
10
29 Views
Last Modified: 2016-09-27
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!
0
Comment
Question by:trs28
10 Comments
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 250 total points
ID: 41818269
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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41818272
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 41818274
@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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41818281
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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41818283
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41818299
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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41818308
Pls wait.
0
 
LVL 18

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 250 total points
ID: 41818357
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
 

Author Comment

by:trs28
ID: 41818464
Thanks everyone for the assistance!   I knew I was doing something stupid and staring me right in the face the whole time!
0
 

Author Closing Comment

by:trs28
ID: 41818467
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now