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.