Transact-SQL Stored Procedure in SQL Server

Transact-SQL Stored procedure is SQL Server procedure written using T-SQL language and it has to be a single batch of code or we can’t have several GO statements in a single stored procedure. T-SQL Stored procedures accept parameters and return a result set or a single value using OUTPUT parameters.
It is possible to create, modify and drop table and indexes in a stored procedure. We could encapsulate application logic into stored procedure. There is a limitation that we cannot use the USE [databaseName], create aggregate, rule, default, create function, trigger, procedure, or view. But you could workaround almost all restriction using dynamic SQL for transact stored procedures in SQL Server (see example below).
To create stored procedure

CRATE PROCEDURE @paramenterName AS dataTypeName
AS
BEGIN
	SELECT *
	FROM table1;
END

Or to take an actual example let us create a stored procedure on AdventureWorks database that will accept the customerID and return the first and last name as well as customer Account.

USE AdventureWorks2012
GO
 
IF OBJECT_ID ( 'CustomerAcount_Proc', 'P' ) IS NOT NULL 
    DROP PROCEDURE CustomerAcount_Proc;
GO
CREATE PROCEDURE CustomerAcount_Proc ( @CustomerID AS INT)
AS
	SET NOCOUNT ON;
	SELECT P.FirstName,P.LastName,C.AccountNumber
	FROM [Person].[Person] P
		 JOIN [Sales].[Customer]  C 
		 ON P.BusinessEntityID = C.PersonID
	WHERE C.CustomerID = @CustomerID	;
GO

Notice above we don’t necessarily need BEGIN AND END key words for the stored procedure.
If we want to execute the above stored procedure we could use EXECUTE or EXEC keywords and let use do that for customerID 21139

--Exectue as 
EXECUTE CustomerAcount_Proc 21139;
GO

To return a single value from a stored procedure we could use an OUTPUT key word.

--Return a single value
IF OBJECT_ID ( 'TotalCustomers_Proc', 'P' ) IS NOT NULL 
    DROP PROCEDURE TotalCustomers_Proc;
GO
CREATE PROC TotalCustomers_Proc ( @Total AS INT = 0 OUTPUT)
AS	
	SELECT @Total = COUNT(DISTINCT CustomerID) 
	FROM [Sales].[Customer]  C;	
GO

Notice we could use PROC instead of the full PROCEDURE key word or we could use OUT in place of OUT.
We need to include the reserved key word OUTPUT or OUT for the parameter when we call the procedure otherwise we it will return null.

--Execute as 
DECLARE @TotalCount AS INT;
EXEC TotalCustomers_Proc @TotalCount OUTPUT
SELECT @TotalCount AS 'TotalCount'
GO

Sometimes we need to change the definition of our stored procedure we could do that using ALTER keyword. For example let us add a middle name for the our previous stored procedure as

--Change the defination of stored procedures
ALTER PROCEDURE CustomerAcount_Proc ( @CustomerID AS INT)
AS
	SELECT P.FirstName,P.MiddleName,P.LastName,C.AccountNumber
	FROM [Person].[Person] P
		 JOIN [Sales].[Customer]  C 
		 ON P.BusinessEntityID = C.PersonID
	WHERE C.CustomerID = @CustomerID	;
GO

If we no longer need our stored procedure we could as well drop that using DROP keyword as

--Drop stored procedure from the database
DROP PROCEDURE CustomerAcount_Proc;
GO

To avoid most of restriction on the stored procedure we could use dynamic sql. For example let us create a stored procedure on AdventureWorks database that will let us query a customer from AdventureWorksDW data warehouse. Normally we can’t use a keyword USE inside a stored procedure but using dynamic SQL that is possible to do so

--Use dynamic sql to workaround stored procedures
USE AdventureWorks2012
GO 
IF OBJECT_ID ( 'CustomerNameFromDW_Proc', 'P' ) IS NOT NULL 
    DROP PROCEDURE CustomerNameFromDW_Proc;
GO
CREATE PROCEDURE CustomerNameFromDW_Proc (@CustomerKey AS INT)
AS
SET NOCOUNT ON;
DECLARE @Paramenter NVARCHAR(MAX),
		@SQL        NVARCHAR(MAX);
	SET @SQL = N'USE [AdventureWorksDW2012]
			 SELECT C.[CustomerKey], C.FirstName, C.LastName,C.Gender
			 FROM [dbo].[DimCustomer] C
			 WHERE C.[CustomerKey] = @CustKey';
	SET @Paramenter = N'@CustKey INT';
	EXECUTE SP_EXECUTESQL @SQL, @Paramenter, @CustKey = @CustomerKey;
GO

SET NOCOUNT ON reduces the messing between SQL Server and client machine and turning it off generally remove unnecessary network overhead.

--Execute for customerID 11010
EXECUTE CustomerNameFromDW_Proc 11010;
GO

To learn more about stored procedures check out the previous blog.