Author avatar

Chervine Bhiwoo

Writing T-SQL Stored Procedures

Chervine Bhiwoo

  • Feb 5, 2020
  • 7 Min read
  • 247 Views
  • Feb 5, 2020
  • 7 Min read
  • 247 Views
Data
SQL Server Reporting Services (SSRS)
Introduction

8

Introduction

Stored procedures are a collection of Transact-SQL statements stored within the database. They are used to encapsulate oft-used queries, such as conditional statements, loops, and other powerful programming features.

Stored procedures are similar to functions in high-level programming languages. They support both input and output parameters, as well as a return value. Stored procedures can return rows of data or single values.

Types of Stored Procedures

There are two types of stored procedures: 1. System stored procedures 2. Extended stored procedures

Both types are supplied prebuilt within SQL Server. The main difference between the two is that the code for system stored procedures is written in T-SQL and found in the master database included in every SQL Server installation. The code for system extended stored procedures, however, is written in native code, usually C++, and supplied via a dynamic-link library.

Creating Stored Procedures

To create stored procedures, the T-SQL statement CREATE PROCEDURE is used. 

1
2
3
4
CREATE PROCEDURE procedure_name
AS
sql_statement
GO;
sql

The CREATE PROCEDURE statement must be the only one in the T-SQL batch. All statements from the AS keyword until the end of the script or until the end of the batch (using a batch separator such as GO) will become the body of the stored procedure.

Replacing Stored Procedures

You cannot replace a procedure using the CREATE PROC statement. You need to alter it explicitly using an ALTER PROCEDURE statement or dropping and then recreating it.

1
2
3
4
ALTER PROCEDURE procedure_name
AS
sql_statement
GO;
sql

Executing Stored Procedures

The EXECUTE statement is used to execute stored procedures. It is recommended that you qualify the procedure name with the schema name when executing stored procedures. This helps the database engine improve its performance as it does not have to search multiple schemas to find the required procedure. Moreover, it also prevents you from executing the wrong procedure if a database has procedures with the same name in different schemas.

1
EXECUTE  schemaname.procedurename;
sql

Deleting Stored Procedures

To remove a stored procedure from the database, the DROP PROCEDURE statement is used. To drop a system extended stored procedure, the procedure sp_dropextendedproc is used,

1
DROP PROCEDURE schemaname.procedurename;
sql

Parametrized Stored Procedures

Parametrized stored procedures allow you to pass values in and out of stored procedures. This makes stored procedures reuseable.

Input Parameters

Input parameters are used to exchange data between stored procedures and the code that called the stored procedure. Stored procedures accept input parameters just like parameters are passed to functions in higher languages.

1
2
3
4
5
6
7
create procedure getEmployeeDetails
@empid int
as
select name, address, phone
from employee
where emplyeeid = @empid;
Go
sql

Notice how empid is passed inside the stored procedure and used in the query via the parameter of the procedure.

Output Parameters

Output parameters are used to for stored procedures to pass a value back to the caller. To use an output parameter within T-SQL, you must specify the OUTPUT keyword in both the CREATE PROCEDURE statement and the EXECUTE statement.

1
2
3
4
5
6
7
8
9
10
create procedure getEmployeeName
@empid INT
@empname varchar(50)
as
begin
  select @empname =  name
  from employee
  where emplyeeid = @empid;
end
Go
sql

Examples

Consider the table of employees below:

idnameaddressagephone
1domUSA3511111111
2brianUSA3022222222
3lettyUSA3233333333

You can use the following script to setup the test data:

1
2
3
4
5
6
7
8
9
10
11
12
Create table employee (
id int,
name varchar(50),
age int,
address varchar(100),
phone int
);
GO

insert into employee values (1, 'dom', 35, 'USA', 1111111), (2, 'brian', 30, 'USA', 222222), (3, 'letty', 32, 'USA', 3333333)

select * from employee
sql

Example 1: A stored procedure that returns a list of people older than 31 years old.

1
2
3
4
5
CREATE PROCEDURE getmployeeByage
AS
select * from employee
where age > 31;
GO
sql

Execute the following command to call the procedure

1
execute dbo.getmployeeByage
sql
idnameaddressagephone
1domUSA3511111111
3lettyUSA3233333333

Example 2: Modify the procedure getmployeeByage to take age as a parameter instead of hard-coding it in the T-SQL code.

1
2
3
4
5
6
ALTER PROCEDURE getmployeeByAge 
  @age int
AS
select * from employee
where age > @age;
GO
sql

Pay attention to how the parameter @age is used in the where condition of the select statement.

Example 3: Using the procedure getmployeeByage, display all the employees older than 30 years old.

1
execute dbo.getmployeeByAge 30
sql

The result of the execute command would be as follows:

idnameaddressagephone
1domUSA3511111111
3lettyUSA3233333333

Example 4: Write a procedure that returns the average age of employees.

1
2
3
4
CREATE PROCEDURE getAvgAge 
AS
select avg(age)  from employee
GO
sql

When you execute the procedure, you should get 32 as result.

Example 5: Modify the procedure getAvgAge to return the result using an output parameter.

1
2
3
4
5
6
7
ALTER PROCEDURE getAvgAge 
  @avgage int OUTPUT
AS
BEGIN
select @avgage = avg(age)  from employee
END
GO
sql

Pay attention to the select statement. Here, the value of the parameter @avgage is set by the line @avgage = avg(age).

Example 6: Call the stored procedure getAvgAge and display the result from the output parameter.

1
2
3
declare @avgageRes int
execute dbo.getAvgAge @avgage = @avgageRes output
select @avgageRes
sql

To display the value from the output parameter, you must first declare a variable (@avgageRes), set the output from the procedure to the variable you just declared (@avgage = @avgageRes) and finally, select the result for display.

Conclusion

In this guide, you learned about the basics of stored procedures. This should help you get started and develop more complex understanding going forward.

To learn more about T-SQL, you can also read the following guides:

Happy Coding!

8