Help with an IF in SQL SERVER Stored procedure

Hi there,

can anyone help me with construction of an IF in a stored procedure in sql server.

Basically I have a simple stored procedure but I now need to pass in a new input parameter which depending if it is true I pass the value D and if its false I pass the value A. But the change is in the middle of a subquery.. let me explain... here is the stored procedure. basically if I send in True for ReturnOldStatus I execute the subquery ItemStatus='D' and if it is false then i pass in ItemStatus='A'

CREATE PROCEDURE [dbo].[MyTempStoredProc]
(
 @IdOffice                                 Int,
 @ReturnOldStatus       bit
)
AS
BEGIN
   SET NOCOUNT ON;
   SELECT * FROM Offices

   WHERE
   IdOffice = @IdOffice 

   AND (V.OffType NOT IN (
      SELECT *  FROM MiscOff 
      WHERE
ItemStatus= 'D') // This needs to be ItemStatus ='A' if FALSE is passed in on the input param

Any ideas??

Thanks

From stackoverflow

• I think this will suffice for your problem. If not, look into the DECLARE/SET statements in TSQL.

  CREATE PROCEDURE [dbo].[MyTempStoredProc] 
      (@IdOffice Int, 
       @ReturnOldStatus bit ) 
  AS BEGIN 
      SET NOCOUNT ON; 
  
      SELECT * FROM Offices 
      WHERE IdOffice = @IdOffice  
            AND (V.OffType NOT IN (SELECT * FROM MiscOff 
                                   WHERE (ItemStatus= 'D' AND @ReturnOldStatus = 1)
                                           OR
                                         (ItemStatus= 'A' AND @ReturnOldStatus = 0) 
                                  )
  

• I would solve it like this:

      declare @itemStatus varchar(1);
      if (@inputParam = 'FALSE')
      begin
          set @itemStatus = 'A'
      end
      else
          set @itemStatus = 'D'
  
     SELECT * FROM Offices
     WHERE
            IdOffice = @IdOffice      
            AND (V.OffType NOT IN (
                     SELECT *  FROM MiscOff 
                     WHERE ItemStatus= @itemStatus) 
                )
  

  T-Sql is not my native language, so there may be errors in there...

• Just use the T-SQL if statement:

  IF @ReturnOldStatus = 0
      BEGIN
          --Some statements
      END
  ELSE
      BEGIN
          --Some other statements
      END
  

  mark smith : thanks but this would mean i need to duplicate the sql statements wouldn't it..??

  Oded : Correct, most of the SQL would have to be duplicated. There are other options, depending on exactly what you need to accomplish. The answer from Frederik does not require code duplication and appears to also solve the problem.