July 25, 2008 at 10:48 am
Hello
I have a report i developed, and end users will like to run the report within a date range.
Once they have gotten their results, they will also like to narrow down their search results with three optional parameters.
The outcome of the query is that, when a user runs a report, the first and second parameters which are dates is a compulsory selection (This done within SSRS), the remaining three parameters, are option if the users wants to narrow down his/her search., based on OrderID, CustomerID or CPAuditID
This is the query am using:
DECLARE @StartDate DATETIME, @EndDate DATETIME, @OrderID NVARCHAR (40), @CustomerID NVARCHAR (40), @ICPAuditID NVARCHAR (40)
SET @StartDate = datetime
SET @EndDate = datetime
SET @OrderID = NULL
SET @CustomerID = NULL
SET @ICPAuditID = NULL -- <-- thanks Jack
SELECT h.OrderID, h.CustomerID, h.ShipCountry, a.[ID],
CONVERT (MONEY, a.Amount) AS Amount,
a.TransactionType, a.TransactionResult, a.ICPBatchNumber,
CONVERT (NVARCHAR (20), a.RecordCreated, 107) AS [RecordCreated],
a.MerchantNumber, a.SchemeName
FROM T_OrderHeader h
INNER JOIN T_Payments p ON h.OrderID = p.OrderID
INNER JOIN T_ICPAudit a ON p.CCNumber = a.CardNumber COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE RecordCreated BETWEEN @StartDate AND @EndDate
AND (
h.OrderID = @OrderID
OR h.CustomerID = @CustomerID
OR a.[ID] = @ICPAuditID
)
----------------------------
THIS IS THE CREATION OF THE STORED PROCEDURE:
CREATE PROCEDURE OrderID
@StartDate DATETIME,
@EndDate DATETIME,
@OrderID NVARCHAR (40) = NULL,
@CustomerId NVARCHAR(40) = NULL,
@ICPAuditID NVARCHAR(40) = NULL
AS
SELECT
T_OrderHeader.OrderID,
T_OrderHeader.CustomerID,
T_OrderHeader.ShipCountry,
T_ICPAudit.ID,
CONVERT (MONEY, (T_ICPAudit.Amount)) AS Amount,
T_ICPAudit.TransactionType,
T_ICPAudit.TransactionResult,
T_ICPAudit.ICPBatchNumber,
CONVERT (NVARCHAR (20), (T_ICPAudit.RecordCreated), 107) AS [RecordCreated],
T_ICPAudit.MerchantNumber, T_ICPAudit.SchemeName
FROM
T_OrderHeader INNER JOIN
T_Payments ON
T_OrderHeader.OrderID = T_Payments.OrderID INNER JOIN
T_ICPAudit ON
T_Payments.CCNumber = T_ICPAudit.CardNumber COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE
(RecordCreated BETWEEN (@StartDate) AND (@EndDate)) AND
(T_OrderHeader.OrderID = CASE WHEN @OrderId IS NULL THEN T_OrderHeader.OrderID ELSE @OrderID END) AND
(T_OrderHeader.CustomerID = CASE WHEN @CustomerID IS NULL THEN T_OrderHeader.CustomerID ELSE @CustomerID END) AND
(T_ICPAudit.ID = CASE WHEN @ICPAuditID IS NULL THEN T_OrderHeader.ICPAuditID ELSE @ICPAuditID END
CAN ANYONE HELP
July 28, 2008 at 5:54 am
Hi
The way I overcome this problem is to put a default value of % in each of the optional parameters and to train the end users that this value is what they need to input to effectively ignore the parameter.
There is no way that I have found to simply mark the parameter as optional.
The other thing you could try is to allow null values on those parameters, however, if you do this then you will need to alter the WHERE clause on your SP so that it recognises a NULL:
WHERE Date Between @StartDate and @EndDate
AND (@OrderId IS NULL or @OrderId=tablename.orderid)
AND ........
AND ........
I do find the first option to be the easiest.
Hope this helps,
Nigel West
UK
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply