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_ControlVariableC
@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!
Here is the SP ....
ALTER PROCEDURE [dbo].[sp_ControlVariableC
@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!
SQL
Last Comment
trs28
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
@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
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
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
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
Pls wait.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks everyone for the assistance! I knew I was doing something stupid and staring me right in the face the whole time!
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.
Open in new window