Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2005
    Posts
    74

    Unanswered: Help with WHERE Clause

    hi guys help please..I have a stored procedure below that basically retrieve data from tables and under my WHERE clause I want to execute conditions depending on the value of "@FilterBy" variable. If @FilterBy is equal to "Pending" then execute a conditions under "IF @FilterBy = 'Pending'" and if it equals to 'Delivered' then execute conditions under IF @FilterBy = 'Delivered'. But unfortunately I can't figure out how to do that my stored procedure below just wont work becuase it has an error "Incorrect syntax near the keyword 'IF'"...Any help guys on how to solve this problem? Thanks in advance!

    Code:
    
    USE [CFREEDB]
    GO
    /****** Object:  StoredProcedure [dbo].[usp_DELIVERY_GET]    Script Date: 09/01/2007 12:03:11 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[usp_DELIVERY_GET]
    	@FilterBy varchar(20),
    	@CustomerID int,
    	@FromDate datetime,
    	@ToDate datetime
    AS
    BEGIN
    
    		SELECT DISTINCT Delivery.CustomerID, Customer.Customer_LastName, Customer.Customer_MiddleName, Customer.Customer_FirstName, 
    						Customer.Customer_Company, Customer.Customer_Address, Customer.Customer_ContactNo, Customer.Customer_Discount, Customer_Balance
    			   
    		FROM CFREE_Delivery Delivery
    			 INNER JOIN CFREE_Customer Customer
    				ON Delivery.CustomerID = Customer.CustomerID 
    		WHERE 
    		IF @FilterBy = 'Pending'
    			BEGIN
    				  Delivery.IsDeleted <> 1 AND
    				  Delivery.IsDelivered IS NULL AND
    				  Delivery.IsRemitted IS NULL AND
    				  Delivery_Date BETWEEN @FromDate AND @ToDate 
    			END
    		IF @FilterBy = 'Delivered'
    			BEGIN
    				  Delivery.IsDeleted <> 1 AND
    				  Delivery.IsDelivered IS NOT NULL AND
    				  Delivery.IsRemitted IS NOT NULL AND
    				  Delivery_Date BETWEEN @FromDate AND @ToDate 			
    			END
    
    		ORDER BY Customer.Customer_LastName, Customer.Customer_FirstName, Customer.Customer_MiddleName
    
    END

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    WHERE Delivery.IsDeleted <> 1
      AND Delivery_Date BETWEEN @FromDate AND @ToDate 
      AND (
           ( @FilterBy = 'Pending'
         AND Delivery.IsDelivered IS NULL 
         AND Delivery.IsRemitted IS NULL 
           )
        OR ( @FilterBy = 'Delivered'
         AND Delivery.IsDelivered IS NOT NULL 
         AND Delivery.IsRemitted IS NOT NULL 
           )
          )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •