Stored Procedure in SQL Server

• The stored procedure is a pre-compiled code of SQL statements stored in the database server.
• The stored procedures enable us to write the programming logic where you may pass the parameters and get the output after processing those parameters.
• The output can be in the form of record-sets when using DML (Data Manipulation Language) for inserting, updating, and deleting records.
• As stored procedure code is saved in the database, you may reuse it without re-writing the code – thus saving time.

The following section describes creating the simple stored procedure with single and multiple parameters and executing the stored procedures in the MS SQL Server database.

How to create a simple stored procedure in the query editor?

A stored procedure is created by using the “CREATE PROCEDURE” statement as follows:

CREATE PROCEDURE example_proc

AS

BEGIN

SELECT * FROM sto_employees;

END

GO

After executing the above code by pressing F5 or the “Execute” button, when you refresh the “Programmability” under the database name where you created this, it should display the newly created stored procedure as shown below:

For creating a stored procedure, you may also use the wizard in MS SQL Server under the “Programmability” option.

How to execute a stored procedure?

Before explaining how to pass parameters and get the output, let us first execute the above created stored procedure.

Our example stored procedure contains a single SELECT statement to retrieve data from the sto_employees table.

For executing a stored procedure, write this command in the “query editor”:

This should fetch all rows from our example table as shown below:

Create stored procure with a parameter example

For creating a stored procedure with a parameter, use the parameter name with @ sign and its data type as follows:

This is followed by statements to be executed where you may use this parameter as well.

In the following example, a stored procedure is created that takes a parameter, @name, and used in the SELECT query for retrieving the record of an employee for the given name.

CREATE PROCEDURE ex_proc_param @name varchar(100)

AS

BEGIN

SELECT * FROM sto_employees

WHERE emp_name = @name;

END

GO

Executing this procedure with parameter

Write this line in the query editor and press F5:

The result set based on our example table should be:

The example of using multiple parameters

Each parameter’s data type can be defined along with its name as shown in the example below.

In the example, two parameters are given to create the stored procedure and those are used in the SELECT statement for retrieving the employee data:

CREATE PROCEDURE ex_proc_multi_param @name varchar(100), @sal numeric(8, 2)

AS

BEGIN

SELECT emp_name, emp_age, emp_salary, join_date FROM sto_employees

WHERE emp_name LIKE @name +'%' AND emp_salary >=@sal;

END

GO

Executing the stored procedure:

The result-set:

As we used the LIKE operator, so the query searched for any names starting with the letter ‘J’.

The other criterion is only getting the records for employees whose salary is greater than or equal to 2500.

Both parameter values are provided as calling the stored procedure in the same sequence as those were created.

A stored procedure with UPDATE query example

Suppose, you are using this database with a web application or any other front-end application.

The form used by the users shows current data and also gives the option to update the records.

As a record is updated, the data is refreshed with the updated values.

For that, you may write a stored procedure that returns the data along with the updating option.

In the following example, a stored procedure is created with three queries:

CREATE PROCEDURE ex_proc_update @salary decimal(8,2),@emp_id int

AS

BEGIN

SELECT * FROM sto_employees;

UPDATE sto_employees

SET emp_salary = @salary

WHERE id = @emp_id;

SELECT * FROM sto_employees;

END

GO

Executed SP with two required values:

The result sets before and after execution:

Similarly, you may execute DELETE and INSERT queries inside the stored procedures and pass along the parameters. The values may be taken from the front end.

What happens if values are not passed as per parameters?

For example, as I passed one parameter value for the above-stored procedure i.e:

While it required two parameters, the following error is generated:

If the order of parameters is changed, particularly with different data types; for example, in our second last example, we executed this stored procedure:

If I change the order to this:

It will produce the same result and no error is raised.

Setting the default value of the parameters

For example, this procedure is declared with two parameters with initial/default values:

CREATE PROCEDURE ex_proc_default @emp_name varchar(100) = 'A', @emp_sal numeric(8, 2) = 2000

AS

BEGIN

SELECT emp_name, emp_age, emp_salary, join_date FROM sto_employees

WHERE emp_name LIKE @emp_name +'%' OR emp_salary >=@emp_sal;

END

GO

As I execute it without passing a value i.e.:

It will produce no errors and the query will use the default values of the parameters.

Upon execution of this statement, the result set: