Create Stored Procedures With Optional Parameters

Sometimes you want to create stored procedures in which all the parameters are not required or will be used. You can create optional parameters and give them a value in the procedure. There are two ways to call procedures; you can pass parameters to stored procedures by name or by position. To pass parameters by name you would do something like this
EXEC prTestOptionalParameters @intID =1

To pass parameters by position you would do something like this
EXEC prTestOptionalParameters 1

Just keep in mind that if you pass by position that you don’t mix up the position because that makes for fun debugging time. Another problem with optional parameters is that if you call the procedure by using position for parameters then those parameters have to be at the end of the procedure. Let’s take a look at how this works

--Create the procedure with optional parameters at the end
CREATE PROCEDURE prTestOptionalParameters
@intID INT,
@chvName VARCHAR(80) = NULL

AS
SET NOCOUNT ON
IF @chvName IS NULL
BEGIN
SELECT @intID AS ID, @chvName AS Name,'@chvName is null' AS message
END
ELSE
BEGIN
SELECT @intID AS ID, @chvName AS Name,'@chvName is not null' AS message
END

SET NOCOUNT OFF


--Run the proc without the optional parameter
EXEC prTestOptionalParameters 1

--Run the proc with the second parameter
EXEC prTestOptionalParameters 1,'abc'

--Run the proc without the optional parameter and use name parameters
EXEC prTestOptionalParameters @intID =1

--Run the proc with a null value for the optional parameter
EXEC prTestOptionalParameters 1 ,null


--Let's switch the parameters around
CREATE PROCEDURE prTestOptionalParameters2
@chvName VARCHAR(80) = NULL,
@intID INT

AS
SET NOCOUNT ON
IF @chvName IS NULL
BEGIN
SELECT @intID AS ID, @chvName AS Name,'@chvName is null' AS message
END
ELSE
BEGIN
SELECT @intID AS ID, @chvName AS Name,'@chvName is not null' AS message
END

SET NOCOUNT OFF


--Run the proc without the optional parameter
EXEC prTestOptionalParameters2 1

You will get this error message
Server: Msg 201, Level 16, State 4, Procedure prTestOptionalParameters2, Line 0Procedure 'prTestOptionalParameters2' expects parameter '@intID', which was not supplied.

--Run the procedure with the named parameter
EXEC prTestOptionalParameters2 @intID =1