T-SQL Tutorial

T-SQL Stored Procedures


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.

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