Stored Procedure Optional Parameter

Often, the SQL developer will need to create a stored procedure with an optional parameter. I recently had to do this and could not quite remember the syntax/method. Below is an example of the technique I prefer:

CREATE PROCEDURE YourStoredProcName
(
@ParamNullable = null
)
AS

BEGIN
SET NOCOUNT ON;
SELECT COLUMN1, COLUMN2
FROM TABLE1
WHERE COLUMN1 = COALESCE(@ParamNullable, COLUMN1)

END
GO

How does this work?

• @ParamNullable = null
  This allows the parameter ParamNullableto be null
• WHERE COLUMN1 = COALESCE(@ParamNullable, COLUMN1)
  ‘COALESCE’ replaces the first value passed to it with the second value if the first value is null,
  so if @ParamNullable is passed into our stored procedure as null, the resuling where clause will be WHERE COLUMN1 = COLUMN1