---

Search posts under SQL Server:

---

Create stored procedure that accepts optional parameter in SQL Server

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 while creating a stored procedure.

CREATE PROCEDURE [dbo].[GetAllPersonalDetails]
       @personalDetailsId 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 @personalDetailsId is null
              BEGIN
                     SELECT * FROM PersonalDetails
              END
       ELSE
              BEGIN
                     SELECT * FROM PersonalDetails
                           WHERE PersonalDetailsId = @personalDetailsId
              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 GetAllPersonalDetails
-- With parameter
EXEC GetAllPersonalDetails 1