Help with IF Statement

• Polymorphist

  SSCommitted

  Points: 1909

  More actions

  July 24, 2011 at 11:11 pm

  #241301

  Hello All,

  I don't work with IF statements in SQL very often and i've run into something which should be easy but is confusing me. I feel like an idiot, but since I don't use IF or IF/ELSE statements in SQL very often i'm probably missing something.

  I thought that in T-SQL you could write a statement such as this one:

  DECLARE @Test1BIT

  ,@Test2 BIT

  SET @Test1 = 1

  SET @Test2 = 2

  IF @Test1 = 1 AND @Test2 = 2

  PRINT 'IF Option 1 Selected'

  Note the IF condition 1 AND condition 2. However, when I run this in Management Studio all I get is "Command(s) Completed Successfully" rather than the printed statement. I'm confused by this - why doesn't the PRINT statement return?

  The reason I am asking is because I have a large stored procedure that has to execute certain parts of the code based on the contents of bit variables.

  IF @variable1 = 1 AND @variable2 = 1

  <execute this block of code>

  IF @variable1 <> 1 AND @variable2 = 1

  <execute this block of code>

  IF @variable1 = 1 AND @variable2 <> 1

  <execute this block of code>

  ...you see where i'm going with this. I have had the most frustrating time trying to get this part of my procedure to work that i'm to the point that i'm not sure what else to do.

  I can't seem to find a good explanation of this anywhere beyond just very simple examples, which in itself is very frustrating. Can someone help?

  Thanks!

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  July 24, 2011 at 11:23 pm

  #1357609

  You do not want to declare your variables as bit.

  DECLARE @Test1 int

  ,@Test2 int

  SET @Test1 = 1

  SET @Test2 = 2

  SELECT @Test1 AS Test1

  SELECT @Test2 AS Test2

  IF @Test1 = 1 AND @Test2 = 2

  PRINT 'IF Option 1 Selected'

  Was there more to your question?

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Paul White

  SSC Guru

  Points: 150468

  More actions

  July 25, 2011 at 12:03 am

  #1357618

  The BIT data type can hold the values 0, 1, or NULL.

  See http://msdn.microsoft.com/en-us/library/ms177603.aspx

  Converting to bit promotes any nonzero value to 1.

  See http://msdn.microsoft.com/en-us/library/ms191530.aspx

  In many cases, it is possible to write a single declarative statement rather than using IF to choose from separate code blocks to run.

  Paul White
  All articles available on SQL.kiwi
  @SQL_Kiwi

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  July 26, 2011 at 12:53 pm

  #1358750

  SQLkiwi (7/25/2011)

  ---

  Converting to bit promotes any nonzero value to 1.

  See http://msdn.microsoft.com/en-us/library/ms191530.aspx

  This is true for implicit as well as explicit conversions:

  DECLARE @bit BIT ;

  -- no error on implicit conversion from number greater than one

  SET @bit = 9999 ;

  SELECT @bit AS [bit, implicitly converted] ;

  -- also no error on explicit conversion from number greater than one

  SELECT CAST(9999 AS BIT) AS [bit, explicitly converted] ;

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato