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:
CREATE PROCEDURE example_proc AS BEGIN SELECT * FROM sto_employees; END GO
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:
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”:
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:
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.
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:
EXEC ex_proc_param @name = ‘Ben’
The result-set based on our example table should be:
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:
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:
EXEC ex_proc_multi_param @name = ‘J’, @sal = 2500
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:
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
As this procedure executed with two required values:
EXEC ex_proc_update @salary = 5000, @emp_id = 5
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?
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:
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 is: