What is Stored procedure in SQL?

The stored procedure is a pre-compiled code of SQL statements that is stored in the database server. The stored procedures enable us writing 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 as 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 MS SQL Server database.

How to create a simple stored procedure in query editor?

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

After executing above code  by pressing F5 or “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:

SQL stored procedure

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

How to execute a stored procedure?

Before explaining how to pass parameters and getting the output based on this, let us first execute the above created stored procedure.

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

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

EXEC example_proc;

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

SQL stored procedure table

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:

CREATE PROCEDURE ex_proc_param @name varchar(100)

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.

Executing this procedure with parameter

Write this line in the query editor and press F5:

EXEC ex_proc_param @name = ‘Ben’

The result-set based on our example table should be:

SQL procedure parameter

The example of using multiple parameters

The stored procedure with multiple parameters can be created by using the parameter names separated by a comma. 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 as creating the procedure and those are used in the SELECT statement for retrieving the employee data:

Executing the stored procedure:

EXEC ex_proc_multi_param @name = ‘J’, @sal = 2500

The result-set:

SQL procedure multiple parameter

As we used LIKE operator, so the query searched any names starting with letter ‘J’. The other criterion is only getting the records for employees which 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 as well as 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 updating option.

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

As this procedure executed with two required values:

EXEC ex_proc_update @salary = 5000, @emp_id = 5

The result-sets before and after execution:

SQL procedure update

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?

As you try executing a stored procedure with required parameters with less or more values, it will generate an error. For example, as I passed one parameter value for the above stored procedure i.e:

EXEC ex_proc_update @salary = 6500

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

Msg 201, Level 16, State 4, Procedure ex_proc_update, Line 0 [Batch Start Line 11]

Procedure or function ‘ex_proc_update’ expects parameter ‘@emp_id’, which was not supplied.

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

EXEC ex_proc_multi_param @name = ‘J’, @sal = 2500

If I change the order to this:

EXEC ex_proc_multi_param @sal = 2500, @name = ‘J’

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

Setting the default value of the parameters

For avoiding the errors if no value is passed for the parameters, you may use the default values as creating the stored procedure. For example, this procedure is declared with two parameters with initial/default value:

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

EXEC ex_proc_default

It will produce no errors and the query will use the default values of the parameters. Upon execution of this statement, the result-set is:

SQL procedure defaults