How To Create A SQL Stored Procedure With An Input Parameter

Learning to create and manage stored procedures in SQL is another useful SQL databases skill for the DBA and developer. The Procedures allow you to create repeatable code that can be executed using just a database single call. We can also design the procedure to handle passing parameters into it.

With the use of Object Relational Mapping (ORM) there are some developers that rarely get into creating stored procedures. Brent Ozar has a short write up on ORM and Stored Procedures with some of the SP and ORM concerns during design.

Why Use Stored Procedures In SQL?

Create reusable code for business logic
Consolidate statements
Do more with one databases call/connection
Compiled in SQL and uses cached execution plans
Queries tend to execute faster

Simple Statement Example

I always start any procedure for queries with the simple or basic statement as a stand-alone. once the statement has been written and tested you can enclose it in a procedure and begin adding parameters or just create it.

Using the Adventure Works 17 database from Microsoft we create this basic SQL statement.

SELECT
	soh.CustomerID,
	pa.AddressLine1,
	pa.City,
	sod.[SalesOrderID],
        sod.[CarrierTrackingNumber],
	pp.[Name]
FROM [Sales].[SalesOrderHeader] AS soh 
INNER JOIN [Sales].[SalesOrderDetail] AS sod
	ON soh.SalesOrderID = sod.SalesOrderID
INNER JOIN [Person].[Address] AS pa
	ON pa.AddressID = soh.BillToAddressID
INNER JOIN [Production].[Product] AS pp
	ON pp.ProductID = sod.[ProductID]

WHERE sod.[SalesOrderID] = 43873

To turn this into a stored procedure we only need to add the CREATE statement and a BEGIN – END to the code. Were going to call this procedure “sp_Get_Address_Tracking” and were going to keep the Sales Order ID (43873) static for right now.

Its a good practice to preface the procedure name with “something” that identifies as a procedure. IN this case we are using “sp_”.

CREATE PROCEDURE [sp_Get_Address_Tracking]
AS
BEGIN
	SELECT
		soh.CustomerID,
		pa.AddressLine1,
		pa.City,
		sod.[SalesOrderID],
		sod.[CarrierTrackingNumber],
		pp.[Name]
	FROM [Sales].[SalesOrderHeader] AS soh 
	INNER JOIN [Sales].[SalesOrderDetail] AS sod
		ON soh.SalesOrderID = sod.SalesOrderID
	INNER JOIN [Person].[Address] AS pa
		ON pa.AddressID = soh.BillToAddressID
	INNER JOIN [Production].[Product] AS pp
		ON pp.ProductID = sod.[ProductID]
	WHERE sod.[SalesOrderID] = 43873

END

Run this code to create the new procedure but don’t close the query window yet. Open a new query connected to the current databases and test the procedure by just typing EXECUTE and its name to see the same results as the original query using the same Sales Order ID… 43873.

EXECUTE sp_Get_Address_Tracking;

Input Parameters

Now we need to change the Sales Order ID to something else but we don’t want to modify the procedure each time. We will create parameter that can be passed into the procedure and used in the query.

Go back to the procedure CREATE window and change the CREATE PROCEDURE line to ALTER PROCEDURE like this, and execute.

CREATE PROCEDURE [sp_Get_Address_Tracking]
ALTER PROCEDURE  [sp_Get_Address_Tracking]

Now we can make changes to the stored procedure code in this window and test in the other window that has “EXECUTE sp_Get_Address_Tracking; “.

Here we are going to focus on the WHERE statement and new parameters in our procedure. We want to be able to submit any sale order ID to the procedure as a parameter and get back the same information for it. Modify the CREATE PROCEDURE and add a parameter to it.

Notice this is before the “AS”…

CREATE PROCEDURE [sp_Get_Address_Tracking]
	@orderid INT
AS

The parameter is called “@orderid” and with this change the procedure will not run without it being provided. At the end of the statement add the parameter to the WHERE clause to complete the code change.

WHERE sod.[SalesOrderID] = 43873
WHERE sod.[SalesOrderID] = @orderid

Click execute to save the stored procedure and go back to the query window to EXECUTE it. This time you will need to provide the SalesOrderID to get the procedure to run.

EXECUTE sp_Get_Address_Tracking @orderid = 43873;

EXECUTE sp_Get_Address_Tracking @orderid = 43659;

EXECUTE sp_Get_Address_Tracking 43892;

You can add more parameters to the procedure in the same way to make it more of a dynamic DB call. When calling the procedure the parameters are assigned in order so for “best practice” I recommend using the param name each time it is used in your application code.

EXECUTE sp_Get_Address_Tracking 
     @param1 = 1234,
     @param2 = 'Testing',
     @param3 = null;

Default Parameter

One last option to cover the basics is the ability to assign a default value to the parameters when you create your stored procedure. The value must match the datatype used such as a string or integer.

You can create more than 1 parameter and they do NOT need to be required or used in the procedure at all. You can define them with a default and use them when necessary or just run the procedure and it uses the default.

As an example lets alter the procedure again and use the same number as before, assign it “by default” to the @orderid parameter.

	@orderid INT
	@orderid INT = 43873

You can now execute the procedure without including the parameter as we have now assigned a default value to it. It will return the same values we have been seeing using the 43873 order ID.

EXECUTE sp_Get_Address_Tracking;

Now we, sorta, have the best of both worlds, if I run the procedure it uses the default value and I get the same result…OR I can provide the parameter with a different value and it overwrites the default.

Try using a different @orderid to get the order details.

EXECUTE sp_Get_Address_Tracking @orderid = 43659;

At this point you can play around with adding and using parameters in your stored procedure. Here are some formatted examples of default values.

Stored Procedure Param Examples

ALTER PROCEDURE [sp_Test_Me]
     @date DATE = '2025-01-05'
AS
BEGIN
	SELECT @date
END;
-----------------------------------------

EXEC [sp_Test_Me]; 
EXEC [sp_Test_Me] @date = '2121-06-15';

-----------------------------------------

ALTER PROCEDURE [sp_Test_Me]
     @orderid INT = 123,
     @corpname VARCHAR(20) = 'Some Text or String', 
     @date DATE = '2025-01-05'
AS
BEGIN
	SELECT @date, @corpname, @orderid
END;
-----------------------------------------


EXEC [sp_Test_Me] ;
EXEC [sp_Test_Me] @corpname = 'OtherText';
EXEC [sp_Test_Me] @date = '2231-03-15', @orderid = 777;