Create SQL Server Stored Procedure Default Parameter

by

In this SQL Server tutorial, I will explain how to create an SQL Server stored procedure default parameter.

Especially you will learn how to create a stored procedure with default parameters and values.

I will explain the proper syntax for creating SQL Server stored procedures with default parameters.

SQL Server Stored Procedure Default Parameter

SQL Server stored procedure default parameters, which means here you need to specify the default parameter for the stored procedure while defining it.

Default parameters in SQL Server stored procedure allow you to specify default values for parameters. When executing the stored procedure without value for a parameter with a default value, SQL Server uses the default value defined in the stored procedure.

The syntax to create a stored procedure with a default parameter is below.

CREATE PROCEDURE ProcedureName
	@Parameter1 DataType = DefaultValue,
	@Parameter2 DataType = DefaultValue,
	...
AS
BEGIN
	-- Procedure body
END

In the above syntax, CREATE PROCEDURE ProcedureName is used to create a new stored procedure named ‘ProcedureNmae’. CREATE PROCEDURE is the command that defines a newly stored procedure in SQL Server.

The next parameter with default values is @Parameter1 DataType = DefaultValue and @Parameter2 DataType = DefaultValue. Here, two parameters are defined for the stored procedure to accept.

Each parameter is prefixed with an ‘@’ symbol, followed by its name (@Parameter1 and @Paramter1), data type ‘DataType’ and an optional default value ‘=DefaultValue’.

Here @Parameter1 DataType = DefaultValue, which declares the first parameter of the procedure. It specifies the parameter’s name, ‘@Parameter1’ and its data type ‘, ‘DataType’, such as INT and VARCHAR.

Then, a default value (‘DefaultValue’). If this procedure is executed without providing a value for ‘@Parameter1’, SQL Server will use ‘DefaultValue’.

Similarly, it declares the second parameter using @Parameter2 DataType = DefaultValue.

Next, you know how to specify the SQL Statement with the procedure body.

Let’s take an example. I have an Employee table, as shown below.

SQL Server Stored Procedure Default Parameter Employees Table

Let’s create a simple stored procedure with default parameters. This stored procedure will return the employees belonging to a specific department.

CREATE PROCEDURE GetEmployeesByDepartment
	@DepartmentName NVARCHAR(50) = 'IT'
AS
BEGIN
	SELECT EmployeeID, Name, Department
	FROM Employees
	WHERE Department = @DepartmentName;
END

When you execute the above query, it creates a GetEmployeesByDepartment stored procedure with default parameters @DepartmentName of type NVARCHAR(50), and its default value is ‘IT’; that means while executing this procedure, if don’t provide the parameter value, then it will use the default value.

Now, call the procedure using the query below to know all the employees in the sales department.

EXEC GetEmployeesByDepartment 'Sales';
Server Stored Procedure Default Parameter

Look, the stored procedure returns all the employees of the Sales Department, but here you have passed the parameter value ‘Sales’. I want you to execute the stored procedure without the parameter value below.

EXEC GetEmployeesByDepartment;
SQL Server Stored Procedure Default Parameter Value

Now, this time, you haven’t passed the parameter value. Still, it used the default parameter value, ‘IT’, that you have defined with the stored procedure, so it returned the result containing two employees of the IT department.

Depending on the stored procedure, you can specify any default parameters with default values.

This is how to create SQL Server stored procedure default parameter.

Conclusion

This SQL Server tutorial taught you how to create SQL Server stored procedure default parameters.

Where you learned how to define the stored procedure and its default value; additionally, you created a stored procedure with a default parameter and values and saw how it works when you don’t pass the parameter value.

You may like to read: