58
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.
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.
To create stored procedures, the T-SQL statement CREATE PROCEDURE
is used.
1 2 3 4
CREATE PROCEDURE procedure_name AS sql_statement GO;
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.
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;
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;
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;
Parametrized stored procedures allow you to pass values in and out of stored procedures. This makes stored procedures reuseable.
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
Notice how empid
is passed inside the stored procedure and used in the query via the parameter of the procedure.
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
Consider the table of employees below:
id | name | address | age | phone |
---|---|---|---|---|
1 | dom | USA | 35 | 11111111 |
2 | brian | USA | 30 | 22222222 |
3 | letty | USA | 32 | 33333333 |
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
1 2 3 4 5
CREATE PROCEDURE getmployeeByage AS select * from employee where age > 31; GO
Execute the following command to call the procedure
1
execute dbo.getmployeeByage
id | name | address | age | phone |
---|---|---|---|---|
1 | dom | USA | 35 | 11111111 |
3 | letty | USA | 32 | 33333333 |
1 2 3 4 5 6
ALTER PROCEDURE getmployeeByAge @age int AS select * from employee where age > @age; GO
Pay attention to how the parameter @age
is used in the where
condition of the select statement.
1
execute dbo.getmployeeByAge 30
The result of the execute command would be as follows:
id | name | address | age | phone |
---|---|---|---|---|
1 | dom | USA | 35 | 11111111 |
3 | letty | USA | 32 | 33333333 |
1 2 3 4
CREATE PROCEDURE getAvgAge AS select avg(age) from employee GO
When you execute the procedure, you should get 32 as result.
1 2 3 4 5 6 7
ALTER PROCEDURE getAvgAge @avgage int OUTPUT AS BEGIN select @avgage = avg(age) from employee END GO
Pay attention to the select statement. Here, the value of the parameter @avgage
is set by the line @avgage = avg(age)
.
1 2 3
declare @avgageRes int execute dbo.getAvgAge @avgage = @avgageRes output select @avgageRes
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.
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!
58