T-SQL stored procedures are objects created in the SQL Server database and consist of SQL statements.
Stored procedures remain saved in the SQL Server database for calling or executed whenever needed.
T-SQL stored procedures are mostly used because they can be reused to save time.
The advantages of Using Stored Procedures
Reduce network traffic
between the server and client.
Improved security
for database objects. Controls what database activities are performed.
Reuse of code
to save time. Database operations can be encapsulated in procedures.
Easier maintenance
. Changes are made only in the stored procedures.
Improved performance
. The first time the procedure is executed it compiles and creates an execution plan which is reused for subsequent executions so that the processing of the procedure takes less time.
Types of Stored Procedures
User-defined
is a procedure created by user in database.
Temporary
- these are user-defined procedures and are stored in tempdb
.
System
- system procedures are included with SQL Server.
Create Procedure
The T-SQL statement Create Procedure
is used to create stored procedures.
CREATE PROCEDURE procedure_name
AS
SQL statement
GO;
Alter Procedure
The T-SQL statement Alter Procedure
is used to modifies stored procedures.
ALTER PROCEDURE procedure_name
AS
SQL statement
GO;
Create Function
The T-SQL statement Create Function
is used to create a user-defined function.
CREATE FUNCTION function_name
RETURNS data_type AS
BEGIN
SQL statement
RETURN value
END;
GO;
Call Stored Procedures
The T-SQL command EXECUTE
is used to execute the stored procedures.
EXEC | EXECUTE procedure_name
GO;
Drop Stored Procedures
The T-SQL command DROP PROCEDURE
is used to drop or delete the stored procedures.
DROP PROCEDURE procedure_name
GO;
Rename Stored Procedures
The T-SQL command sp_rename
is used to rename the stored procedures.
sp_rename 'old_procedure_name', 'new_procedure_name'
GO;
Stored Procedures in Object Catalog Views
To check a procedure uses T-SQL Object Catalog Views.
select * from sys.procedures;
select * from sys.objects where type_desc='SQL_STORED_PROCEDURE';