I am using SQL Server 2000, and I want to write a stored procedure that returns certain values either between 2 dates (start and end) or the user enters a particular string. Its grafts a bit , but the dates are not returning true data
ALTER PROCEDURE proc_CBLSearch
@OrderNo VARCHAR(50) = '',
@BizReqID VARCHAR(50) = '',
@NameOnCard VARCHAR(50) = '',
@DocHolder VARCHAR(50) = '',
@SystemRef VARCHAR(50) = '',
@StartDate DATETIME = 0,
@EndDate DATETIME = 0
AS
IF @StartDate = 0 or @StartDate is null
BEGIN
SELECT @StartDate = 0
END
IF @EndDate = 0 or @EndDate is null
BEGIN
SELECT @EndDate = GETDATE()
END
-- You need to enter the @strSearch
-- This can be BizReqID,DocID, Doc Holder Name, MyMarket Order
SELECT DISTINCT
--CBL.DocumentHeight,
--CBL.DocumentWidth,
--CBL.DocumentUnit,
CBL.SentDate,
CBL.SystemRef,
DocumentDefinition.ItemName,
XMLExtract.DocHolder,
XMLExtract.NameOnCard,
XMLExtract.Company,
XMLExtract.BizcardOrderID,
XMLExtract.BizReqID,
MyMarket.OrderNo
FROM CBL
INNER JOIN Offerings
ON CBL.OfferingID = Offerings.OfferingID
INNER JOIN DocumentDefinition
ON CBL.DocumentDefinitionID = DocumentDefinition.DocumentDefinitionID
INNER JOIN Users
ON CBL.UserID = Users.UserID
LEFT OUTER JOIN XMLExtract
ON CBL.SystemRef = XMLExtract.BizcardOrderID
LEFT OUTER JOIN MyMarket
ON XMLExtract.BizReqID = MyMarket.BizReqID
WHERE (
CBL.PrinterID = 43
OR
CBL.PrinterID = 45
)
AND CBL.BatchStatus = 1
AND
(
(CBL.SentDate BETWEEN @StartDate AND @EndDate)
OR ((
@OrderNo = ''
OR
MyMarket.OrderNo LIKE '%' + @OrderNo + '%'
)
AND (
@BizReqID = ''
OR
XMLExtract.BizReqID LIKE '%' + @BizReqID + '%'
)
AND (
@NameOnCard = ''
OR
XMLExtract.NameOnCard LIKE '%' + @NameOnCard + '%'
)
AND (
@SystemRef = ''
OR
CBL.SystemRef LIKE '%' + @SystemRef + '%'
)
AND (
@DocHolder = ''
OR
XMLExtract.DocHolder LIKE '%' + @DocHolder + '%'
)))
ORDER BY CBL.SentDate