Create Stored Procedure that Accepts Optional Parameter in SQL Server

Updated on August 30, 2015     Kisan Patel

This tutorial will show you how to create stored procedure that accepts optional parameter in SQL Server?

To create optional parameter in stored procedure, we set the parameter value to NULL.

CREATE PROCEDURE [dbo].[GetAllRegionDetailsWithParameter]
@RegionID int = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
	IF @RegionID is null
		BEGIN
			SELECT * FROM Region
		END
	ELSE
		BEGIN
			SELECT * FROM Region WHERE RegionID = @RegionID
		END
	END

The above stored procedure may or may not be passed @personalDetailsId parameter value and it can be called in any of the following way

⇒ Without parameter

EXEC GetAllRegionDetailsWithParameter

⇒ With parameter

EXEC GetAllRegionDetailsWithParameter 1