Friday, February 24, 2012

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!

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

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

No comments:

Post a Comment