Simple IF evaluation mistake in SQL Azure statement
Posted on 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
DECLARE @Enable_Msg varchar(max) = 'error message text';
IF (@Enable <> 0 AND @Enable <> 1) OR @Enable IS NULL
THROW 90002, @Enable_Msg, 1
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!