18. August 2008 20:33
by Chakkaradeep
0 Comments
Today I had the need to have optional parameters in my SQL stored procedure. And It wasn't that complex to implement :)
Below is the code:
ALTER PROCEDURE [dbo].[GetSampleRequests]
@Org varchar(50),
@Type varchar(50),
@From DateTime,
@To DateTime
AS
BEGIN
SELECT
Organisation,
ServiceType,
COUNT(DataStatus) AS DataStatusCount
FROM
SampleLog
WHERE
((@From IS NULL OR @To IS NULL) OR
TransactionDateTime BETWEEN @From and @To) AND
(@Org IS NULL OR Organisation = @Org) AND
(@Type IS NULL OR ServiceType = @Type)
GROUP BY
Organisation,
ServiceType
ORDER BY
Organisation,
ServiceType
END
The magic happens when we check whether the parameter is IS NULL :)