This page has 0 threads | Add post

Passing Parameters to Stored Procedures

Written by Andy Brown

In this tutorial

Reasons for Using Parameters
Creating Parameters
Passing Parameters by Name, not by Position
Setting Default Values for Parameters
Limitations to the Use of Parameters

Parameters are the raison d'etre of stored procedures (if you can't be pretentious in your own tutorial, where can you be?). Let's look first at why you might need them.

Reasons for Using Parameters

Consider the following SQL stored procedure::

CREATE PROC spFilmsByMinLength

-- show all films lasting more than a

-- given number of minutes

SELECT

FilmName,

FilmRunTimeMinutes

FROM

tblFilm

WHERE

FilmRunTimeMinutes>120

This lists out all of the films (or movies) in a given table which last more than 120 minutes, or 2 hours. If you want to change the minimum duration, you'll need to change the procedure.

What parameters allow you to do is to make a procedure flexible, so that you can run it to show (for our example) films lasting more than any given duration, like so:

-- show films lasting more than 3 hours

EXEC spFilmsByMinLength 180

-- show films lasting more than 3.5 hours

EXEC spFilmsByMinLength 210

It's easy to imagine extending this to make a truly flexible stored procedure:

-- show films lasting more than 3 hours,

-- made in 2002 which won no Oscars

EXEC spListFilmsWithParameters 180, 2002, 0

Parameters have one more vital use: they reduce the risk of SQL injection attacks in your system.

Creating Parameters

To create parameters, just list them out in parentheses after the stored procedure name, separated by commas. For our example:

CREATE PROC spFilmsByMinLength(

@MinLength int

)

-- show all films lasting more than a

-- given number of minutes

SELECT

FilmName,

FilmRunTimeMinutes

FROM

tblFilm

WHERE

FilmRunTimeMinutes>@MinLength

This creates a procedure called spFilmsByMinLength which expects to be passed a single value of type int (ie an integer). The procedure then shows all films which lasted more than this number of minutes.

I've blogged separately on the possible data types that you can use in SQL.

Here's another example: the following procedure lists out all films released between two given dates, and whose name contains a given string of text:

CREATE PROC spListFilmsWithParameters (

@MinDate date,

@MaxDate date,

@ContainsText varchar(MAX)

)

-- show films made between given dates, and whose

-- titles contain given text

SELECT

FilmName,

FilmReleaseDate

FROM

tblFilm

WHERE

FilmReleaseDate between @MinDate and @MaxDate and

FilmName like '%' + @ContainsText + '%'

Here's how you could try running this stored procedure:

-- try out this stored procedure

EXEC spListFilmsWithParameters

'01/01/2001', '12/31/2010', 'Shrek'

Passing Parameters by Name, not by Position

Another way to run the stored procedure listed above would be to pass the parameters by name:

-- pass parameters by name

EXEC spListFilmsWithParameters

@ContainsText='Shrek',

@MinDate='01/01/2001',

@MaxDate='12/31/2010'

The obvious advantage of this approach is that you can put the parameters in any order (as I've done here).

Setting Default Values for Parameters

The spListFilmsWithParameters procedure listed above has one flaw: you have to specify a value for every single parameter. It would be good to be able to miss out a parameter and have it take a sensible value. For example, it would be nice to run:

-- pass parameters by name

EXEC spListFilmsWithParameters

@ContainsText='Shrek',

@MinDate='01/01/2001'

and have this return all Shrek films made since the start of the millennium, with no maximum date. To do this, you need to give one or more parameters default values:

CREATE PROC spListFilmsWithParameters (

@MinDate date=null,

@MaxDate date=null,

@ContainsText varchar(MAX)=''

)

-- show films made between given dates, and whose

-- titles contain given text (but let user omit parameters)

SELECT

FilmName,

FilmReleaseDate

FROM

tblFilm

WHERE

(FilmReleaseDate >= @MinDate or @MinDate is null) and

(FilmReleaseDate <= @MaxDate or @MaxDate is null) and

(FilmName like '%' + @ContainsText + '%')

Here's how this works. Suppose you call the procedure, but don't specify the maximum date:

-- omit the MaxDate parameter

EXEC spListFilmsWithParameters

@MinDate='01/01/2001',

@ContainsText='Shrek'

Then the conditon:

-- second of three conditions

@MaxDate is null

is always true (the parameter always equals null), so the second condition above will effectively be ignored.

Likewise, if you omit the @ContainsText parameter the like condition will evaluate to '%%', which will show everything.

Limitations to the Use of Parameters

Many newcomers to SQL (myself included) learn about parameters, and then excitedly try out the following procedure:

CREATE PROC spListRows(

@TableName varchar(MAX)

)

-- list out rows from any table? I think not

SELECT

FROM

@TableName

This won't work! You can't use parameter values as table names, column names or in ORDER BY clauses. Sorry!

There is an exception to this using something called dynamic SQL. In dynamic SQL you build up a statement to be executed and then run it. It does, however, have a number of disadvantages: it's hard to test, runs slowly and makes you liable to SQL injection attacks.

Previous
tutorial

Trapping errors and debugging

Previous
part

Modifying or Changing a Stored Procedure

Top of page

Next
part

Returning Values from Stored Procedures in SQL

Next
tutorial

User-defined functions

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Stored procedures

Passing Parameters to Stored Procedures

Reasons for Using Parameters

Creating Parameters

Passing Parameters by Name, not by Position

Setting Default Values for Parameters

Limitations to the Use of Parameters

Head office

London

Manchester