Creating a SQL Server stored procedure with parameters

 
The actual power of stored procedures is the capability to pass parameters and equipped with the stored procedure to handle the differing requests that are made.

In this tutorial we are going to pass parameter values to a stored procedure.

Description

  • It is very similar to the case where you have the ability to use parameters with your SQL code you are also permitted to setup your stored procedures for accepting one or more parameter values.
  • Let’s discuss three possible cases:

Case 1: Passing Single Parameter
Case 2: Default Parameter Values as an Argument
Case 3: Multiple Parameters

Case 1: Passing Single Parameter

  • In this given example we are going to query the Person.Address table from the AdventureWorks database, however instead of getting back all records we are going to restrict it to just a particular city.
  • This instance assumes there are going to be an exact match on the City value that is passed as an argument.

USE AdventureWorks
GO
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SELECT *
FROM Person.Address
WHERE City = @City
GO
 
Now, to call and execute this stored procedure we will do as follows:
 
EXEC dbo.uspGetAddress @City = ‘New York’
 
We can also perform the same thing, but permit the users to give us a starting point to search to find the data. Here we are allowed to change the “=” to a LIKE and use the “%” wildcard.
 
USE AdventureWorks
GO
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SELECT *
FROM Person.Address
WHERE City LIKE @City + ‘%’
GO

  • In both of the proceeding given examples it assumes that a parameter value will mandatory always be passed. In case you execute the procedure without passing a parameter value you are going to get an error message as follow:
  • Msg 201, Level 16, State 4, Procedure uspGetAddress, Line 0 Procedure or function ‘uspGetAddress’ expects parameter ‘@City’, which was not supplied.

Case 2: Default Parameter Values as an Argument

  • In most scenarios it is always a good habit to pass all parameter values, however sometimes it is not possible. Hence in this given example we are going to use the NULL option to allow you to not pass in a parameter value.
  • In case we create and execute this stored procedure as it is not going to return any data, as it is looking for any City values that is equal to NULL.

USE AdventureWorks
GO
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30) = NULL
AS
SELECT *
FROM Person.Address
WHERE City = @City
GO
 
We could alter this stored procedure and use the ISNULL feature to get around this. Hence if a value is passed it is going to use the value to narrow the output data set and in case value is not passed it is going to return all records.
 
(Kindly Note: in case the City column has NULL values this is not going to include these values. And you need to add additional logic for City IS NULL)
 
USE AdventureWorks
GO
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30) = NULL
AS
SELECT *
FROM Person.Address
WHERE City = ISNULL(@City,City)
GO

Case 3: Multiple Parameters

Setting up multiple parameters is very easy to do. You just need to list each parameter and the data type separated by a comma as shown below.
 
USE AdventureWorks
GO
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30) = NULL, @AddressLine1 nvarchar(60) = NULL
AS
SELECT *
FROM Person.Address
WHERE City = ISNULL(@City,City)
AND AddressLine1 LIKE ‘%’ + ISNULL(@AddressLine1 ,AddressLine1) + ‘%’
GO
 
To execute this you could do any of the following:
 
EXEC dbo.uspGetAddress @City = ‘Calgary’
–or
EXEC dbo.uspGetAddress @City = ‘Calgary’, @AddressLine1 = ‘A’
–or
EXEC dbo.uspGetAddress @AddressLine1 = ‘Acardia’
— etc…

Kristin is a content strategist at Techarex Networks. Kristin follows the B2B technology space closely and loves to write on the latest changes in technology, futuretech and fixes for day to day how to issues. Besides writing Kristin also loves music, moves and skating.