Solved

Simple IF evaluation mistake in SQL Azure statement

Posted on 2016-09-27
10
36 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 28

Expert Comment

by:Pawan Kumar
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 28

Expert Comment

by:Pawan Kumar
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 28

Expert Comment

by:Pawan Kumar
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
 
LVL 28

Expert Comment

by:Pawan Kumar
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 28

Expert Comment

by:Pawan Kumar
ID: 41818308
Pls wait.
0
 
LVL 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

828 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