Stored Procedure Optional Parameter

February 15, 2012

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: