This post is for the beginner to understand how to write and modify a SQL stored procedure with parameters and variables. We will try to include some “why & how” answers that many stored procedure examples leave out.
Procedures can become very complicated so we will keep it simple and build on this first example.
CREATE PROCEDURE [mypr_Camel_Case_Procedure]
AS
BEGIN
SELECT column, column2 FROM [schema].[tablename]
WHERE column2 = 37232
END
We will be working with the [WideWorldImporters] example database from Microsoft so anyone can copy and test.
Stored Procedure With Input Parameters
Let’s look at bringing in values (parameters) so the procedure is dynamic enough to include a decision block and work with different requests that may require similar result sets.
One of the most common parameters would be START and END dates which can also get complicated. We will keep it simple and use the “OrderDate” from the [Purchasing].[PurchaseOrders] table. The idea is to return all of the orders on a certain date with the option to return Orders with a certain Delivery method.
CREATE PROCEDURE pr_Get_PurchaseOrders_By_Order_Date
@OrderDate DATE -- value is required
AS
BEGIN
SELECT
PurchaseOrderID,
SupplierID,
OrderDate,
ExpectedDeliveryDate
FROM [Purchasing].[PurchaseOrders]
WHERE OrderDate = @OrderDate
END
EXEC pr_Get_PurchaseOrders_By_Order_Date @OrderDate = '2013-01-15'
Required and Not-Required Parameters
The Order Date was required so now we are adding another parameter (DeliveryMethodID) that will not be required but we have a default assignment. The procedure will work just like the previous except only the results with a Delivery Method of “10” will be returned.
CREATE PROCEDURE pr_Get_PurchaseOrders_By_Order_Date
@OrderDate DATE,
@DeliveryMethod INT = 10 -- defaulted to 10, not required
AS
BEGIN
SELECT
PurchaseOrderID,
SupplierID,
OrderDate,
ExpectedDeliveryDate
FROM [Purchasing].[PurchaseOrders]
WHERE OrderDate = @OrderDate
AND DeliveryMethodID = @DeliveryMethod
END
If we want a different Delivery Method (7) we can assign this parameter to get it.
EXEC pr_Get_PurchaseOrders_By_Order_Date @OrderDate = '2013-01-15'
EXEC pr_Get_PurchaseOrders_By_Order_Date @OrderDate = '2013-01-15', @DeliveryMethod = 7
Output Parameters
We can also use the INPUT parameter as an OUTPUT parameter and return them together. Here we are adding a parameter for the purpose of returning the most recent Delivery Date along with the number of orders. The second execution is to simply display the value of the Declared variable as it was assigned at execution.
CREATE PROCEDURE pr_Get_PurchaseOrders_Count
@OrderDate DATE,
@MostRecentExpectedDelivery DATE OUTPUT
AS
BEGIN
SET @MostRecentExpectedDelivery = (SELECT MAX([ExpectedDeliveryDate]) FROM [Purchasing].[PurchaseOrders])
SELECT COUNT(OrderDate) AS [Count], @MostRecentExpectedDelivery AS [RecentDelivery]
FROM [Purchasing].[PurchaseOrders] WHERE OrderDate = @OrderDate
END
------ declare variable and execute the procedure -------
DECLARE @MostRecentDelivery DATE
EXEC pr_Get_PurchaseOrders_Count @OrderDate = '2013-01-15', @MostRecentExpectedDelivery = @MostRecentDelivery OUTPUT
SELECT @MostRecentDelivery -- return the variable output
Stored Procedure Example Template
We are following some light coding standards and keeping the declarations and variables near the top along with comments and a BEGIN and END statement. This exact structure is not necessary but easier to read and another developer can understand the procedure purpose more easily.
Expanding on the above, a full stored procedure example with 2 input parameters might look like this:
/****************************************************************
** Author: Akira
** Description:
** Example: EXEC [mypr_Camel_Case_Procedure] @maxdate = '3/1/2050'
****************************************************************/
CREATE PROCEDURE [mypr_Camel_Case_Procedure]
@maxdate DATETIME, -- Parameter must be provided
@mindate DATETIME NULL
AS
SET @mindate = ISNULL(@mindate, GETDATE()) -- set to today if NULL
DECLARE @mytype VARCHAR(200) -- declare internal variable
BEGIN
SET NOCOUNT ON -- does not return numbers as it executes
SET @mytype = (SELECT [thetype] FROM [TableName] WHERE MAX(TN.field1))
-- return the result set
SELECT
TN.field1,
CONVERT(VARCHAR, OTN.field2) AS [new_field2],
OTN.field3,
FROM [schema].[TableName] AS TN
INNER JOIN [schema].[OtherTableName] AS OTN
WHERE OTN.[otherdate] >= @mindate
AND TN.[datefield] <= @maxdate
AND OTN.field3 = @mytype
END