T-SQL Stored Procedures syntax and example

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';