CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
ETA
ETA

Reputation: 59

IF ELSE in a stored procedure when you are Creating a #TempTable

I am trying to get this stored procedure to only return specific rows when a function equals a specific ID number. For example, if @RentalOnly = 'Y' then only return rows where productID is in '3367' or '3367G' and if @RentalOnly = 'N' then return all other rows that don't have this productID in them.

Here is what I have so far. 

alter Procedure [dbo].GetAllProductIDs
    @StartDate datetime,
    @EndDate datetime,
    @RentalOnly char(1)
AS
begin
    SET NOCOUNT ON;

    CREATE TABLE #GetAllProductIDs
    (      
        [Status] nvarchar(30),
        [To Set Date] datetime,
        [Order ID] int,
        [Product ID] nvarchar(10),
        [PPArea] char(2),
        [OrderLineNo] nvarchar(10),
        [OrigSalesman] nvarchar(30),
        [Enterby] int,
        [Rental Month] int,
        [Quantity] int,
        [Quoted$] money,
        [EXT$] money,
        [Mo Base Rental] money,
        [Ext. Base Rental] money,
        [Quoted Mo Rent] money,
        [3367 Total$ vs. Base Total$] money,
        [3367 % Total vs. Base Total] float
    )

    insert #GetAllProductIDs
       exec TempPower_AZ.dbo.GetAllProductIDs @StartDate, @EndDate

    insert #GetAllProductIDs
       exec TempPower_LV.dbo.GetAllProductIDs @StartDate, @EndDate

    insert #GetAllProductIDs
       exec TempPower_OC.dbo.GetAllProductIDs @StartDate, @EndDate

    insert #GetAllProductIDs
       exec TempPower_SD.dbo.GetAllProductIDs @StartDate, @EndDate

    insert #GetAllProductIDs
       exec TempPower_RS.dbo.GetAllProductIDs @StartDate, @EndDate

    if (@RentalOnly = 'Y')
    begin
        Select * 
        from #GetAllProductIDs
        where [Product ID] in ('3367', '3367G')

        return
    end

    if (@RentalOnly = 'N')
    begin
        Select * 
        from #GetAllProductIDs
        where [Product ID] not in ('3367', '3367G')

        return
    end
end

Upvotes: 0

Views: 55

Answers (2)

spencer7593
spencer7593

Reputation: 108410

Q: How do i get it to return rows based on those two values ('Y' or 'N') entered into @RentalOnly and if nothing is entered it will just return all of the productIDs

A: To return all of the productIDs, just run a SELECT without a WHERE clause.

But that's conditional on "if nothing is entered"

What value or values of @RentalOnly would we consider to satisfy the condition "if nothing is entered"? Is that condition satisfied by a NULL? a space? a 'W' ? Any character other than 'Y' or 'N'?

If this is the logic we want to implement:

IF @RentalOnly is 'Y', return the specified subset from the table
ELSE IF @RentalOnly is 'N', return a different subset from the table
ELSE (for all other values of @RentalOnly) return the whole table

As an example of how to implement that logic using IF...ELSE blocks...

IF (@RentalOnly = 'Y')
  Select * from #GetAllProductIDs where [Product ID] in ('3367', '3367G')
ELSE
  BEGIN
    IF (@RentalOnly = 'N')
      Select * from #GetAllProductIDs where [Product ID] not in ('3367', '3367G')
    ELSE
      Select * from #GetAllProductIDs
  END
RETURN

The ELSE isn't strictly necessary. If we code the conditions so that they are are mutually exclusive, we could get an equivalent result with this:

IF (@RentalOnly = 'Y')
  Select * from #GetAllProductIDs where [Product ID] in ('3367', '3367G')
IF (@RentalOnly = 'N')
  Select * from #GetAllProductIDs where [Product ID] not in ('3367', '3367G')
IF (@RentalOnly IS NULL OR @RentalOnly NOT IN ('Y','N')
  Select * from #GetAllProductIDs
RETURN

As DavidG points out in his answer, it's not strictly necessary to use an IF at all. That "if else" logic could be moved into the WHERE clause of a single SELECT statement. For example:

Select * from #GetAllProductIDs
 WHERE ( @RentalOnly IN ('Y') AND [Product ID] IN ('3367', '3367G') )
    OR ( @RentalOnly IN ('N') AND [Product ID] NOT IN ('3367', '3367G') ) 
    OR ( @RentalOnly NOT IN ('Y','N') OR @RentalOnly IS NULL )
RETURN

Upvotes: 1

DavidG
DavidG

Reputation: 118977

I would drop the IF/ELSE logic completely and just do it all in a single query like this:

SELECT
    *
FROM 
    #GetAllProductIDs
WHERE 
    (@RentalOnly = 'Y' AND productID IN ('3367', '3367G')) 
OR  (@RentalOnly = 'N' AND productID NOT IN ('36367, '3367G'))

Upvotes: 1

Related Questions