December 12, 2004 at 1:46 pm
I have one report on sql report server based on this query.
SELECT dbo.TestLog.TestLogID, dbo.TestLog.SerialNumber, dbo.TestLog.TestDateTime,
dbo. TestLog.DUTCategory
FROM dbo.TestLog
WHERE (dbo.TestLog.TestDateTime >= @Beginning_Date) AND (dbo.TestLog.TestDateTime <= @Endind_Date+1) And
(dbo.DUTType.DUTType = @Type)
I am passing two parameter one is Date and other is Type.
2) when i run this query i am passing two parameter one for date and other for Type. What i want a do. If i dont enter any value for Type parameter i want a run the query contain all type for given date. Right now in report server it won't run the query untill i define the value for both parameter.
December 12, 2004 at 4:22 pm
This solution is good for multiple parameters that may or may not be passed.
I haven't seen your Proc so here is a potentially similar one. This procedure set defaults to null where appropriate:
Create Procedure MyTestProc
(
@Beginning_Date DateTime = null,
@Ending_Date DateTime = null,
@Type varchar(50)= null
)
as
SELECT dbo.TestLog.TestLogID,
dbo.TestLog.SerialNumber,
dbo.TestLog.TestDateTime,
dbo. TestLog.DUTType
FROM dbo.TestLog
WHERE (dbo.TestLog.TestDateTime >= COALESCE(@Beginning_Date, '01/01/1900'))
AND (dbo.TestLog.TestDateTime <= COALESCE(@Ending_Date+1, '01/01/2900'))
AND (dbo.TestLog.DUTType = COALESCE( @Type,dbo.TestLog.DUTType))
notice: COALESCE(@Type,dbo.TestLog.DUTType)
What this does is set dbo.TestLog.DUTType = dbo.TestLog.DUTType when no @Type is passed, the result of which is always true
The other COALESCE statements will allow you to:
EXEC MyTestProc
and get all dates and types back.
Hope this helps.
Matthew Spare
ithinkdesigns.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply