Optional Filtering in Stored Procedures

I've seen this little trick in some of our stored procedures for optional filtering. There's a running example below, but the interesting line of code is in the where clause of the select statement:

WHERE (@IsBroadsheet is null OR IsBroadsheet = @IsBroadsheet)

@IsBroadsheet is the filtering parameter passed into the stored procedure. If you pass in null the filter will be ignored. If a value is supplied the result set will be filtered by that parameter. An elegant solution I think.

This example runs on SQL Server 2008:

IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'NewsPapers', N'U')) DROP TABLE NewsPapers;
IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'GetNewsPapers', N'P')) DROP PROCEDURE GetNewsPapers;
    
CREATE TABLE NewsPapers
(
    Id INT CONSTRAINT PK_Newspaper PRIMARY KEY IDENTITY (1,1),
    Title NVARCHAR(100) NOT NULL,
    IsBroadsheet BIT NOT NULL
)

INSERT INTO NewsPapers(Title, IsBroadsheet) VALUES
                         ('Times', 1),
                         ('Sun', 0),
                         ('Mail', 0),
                         ('Telegraph', 1),
                         ('Mirror', 0)
GO
                      
CREATE PROCEDURE GetNewsPapers
    @IsBroadsheet BIT
AS
    SELECT *
    FROM NewsPapers
    WHERE (@IsBroadsheet is null OR IsBroadsheet = @IsBroadsheet)
GO

EXEC GetNewsPapers null;
EXEC GetNewsPapers 1;
EXEC GetNewsPapers 0;