Help with an IF in SQL SERVER Stored procedure

Question

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

Answer 1

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) 
                                )

Answer 2

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...

Answer 3

Just use the T-SQL if statement:

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