SQL Procedure

This page covers SQL Stored Procedure.

The concept of procedures is very similar to functions in many other programming languages.

Stored procedures allow saving and calling of code as well as input and output of parameters.

This tutorial encompasses:

stored procedures
SP with one parameter
SP with multiple parameters

Let’s use the table “student_info” from the previous tutorials.

id	firstName	lastName	age
1	John	Smith	21
2	Patrick	Casey	26
3	Sam	Adams	19
4	Mike	Garcia	NULL
5	Patrick	Dumont	21

Stored Procedure

With procedures, we can write and save code, and then call and execute it later on.

We can also use input and output parameters. Note each procedure stores its parameters locally. As such, the same parameter can be used in multiple stored procedures.

The keywords are “create procedure as“. To execute is “exec“.

The following is an example.

CREATE PROCEDURE count_records
AS
SELECT COUNT(*) FROM student_info;

If no errors, the message “Commands completed successfully” will appear.

Now let’s execute the procedure.

EXEC count_records;

No column name
5

The outcome is 5, as we have 5 record rows in the “student_info” table.

SP with one parameter

Stored procedures also allow parameter usage.

We need to assign parameters with the “@” symbol as well as specify the data type.

Instead of counting all data in the table, we can specify a column.

Below is an example.

CREATE PROCEDURE count_specific_records
@column varchar(15)
AS
SELECT COUNT(@column) FROM student_info;

The “@column” after the name of the procedure refers to the creation of the parameter.

Then we place the parameter where we need it in the query (“SELECT COUNT(@column)”. With this 2nd parameter, we can now write any specific column we need. Both examples below will work.

EXEC count_specific_records @column = 'age';

EXEC count_specific_records 'age';

No column name
5

We have 5 records under the “age” column, hence the output. We can now check the count of any column separately, instead of creating new stored procedure every time.

SP with multiple parameters

We can create procedures with more than 1 parameter.

This can boost procedures’ efficiency significantly.

Let’s see an example.

CREATE PROCEDURE counting
@column varchar(15),
@position1 int,
@position2 int
AS
SELECT COUNT(@column) FROM student_info
WHERE id BETWEEN @position1 AND @position2;

EXEC counting 'age', 2, 5

No column name
4

The result is 4, as we have specified the student id to be between 2 and 5 (including both).

Let’s look at another example.

The following procedure searches and counts pre-specified names.

CREATE PROCEDURE search_name
@name varchar(15)
AS
SELECT firstName, COUNT(firstName) AS [Number of Names] FROM student_info
WHERE firstName LIKE '%' + @name + '%'
GROUP BY firstName;