Solved

Simple IF evaluation mistake in SQL Azure statement

Posted on 2016-09-27
10
32 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 24

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
 
LVL 24

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 24

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 24

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 24

Expert Comment

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

911 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

26 Experts available now in Live!

Get 1:1 Help Now