in .NET

Optional Parameters in SQL Server Stored Procedures

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 🙂

Write a Comment

Comment

Time limit is exhausted. Please reload the CAPTCHA.