Optional parameters in a T-SQL stored procedure

Sometimes it’s really important to use optional parameter in Stored Procedure. For a beginner, it’s definitely a helpful if he/she practices optional parameters in Stored Procedure. For illustration the scenario here is an example.
USE TSQL2012

CREATE PROCEDURE GET_EMP_SP (
@FirstName    VARCHAR (20) = NIULL,
@LastName    VARCHAR (20) = NIULL,
@Title        VARCHAR (20) = NIULL
) AS

set nocount on

BEGIN
Select * from [HR].[Employees] WHERE
(FirstName = ISNULL(@FirstName, FirstName) OR COALESCE(@FirstName, FirstName, ”) = ”)
OR (LastName = ISNULL(@LastName, LastName) OR COALESCE(@LastName, LastName, ”) = ”)
OR (Title = ISNULL(@Title, Title) OR COALESCE(@Title, Title, ”) = ”)

END
set nocount off

GO

EXEC GET_EMP_SP ‘Sara’

Advertisements
This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

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