Stored Procedure With Input And Output Parameters In SQL

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

---