SQL stored procedures and passing parameters
Part five of a six-part series of blogs

Stored procedures are programs in SQL which allow you to pass parameters to queries and manipulate sets of rows in tables. Find out how to write them with this online training blog!

  1. Stored Procedures in SQL
  2. Creating Simple Stored Procedures
  3. Running Stored Procedures
  4. Modifying or Changing a Stored Procedure
  5. Passing Parameters to Stored Procedures (this blog)
  6. A Worked Example of a Longer Stored Procedure

This blog is part of a complete SQL Server tutorial, and is also referenced from our ASP.NET online training blog.

Posted by Andy Brown on 24 May 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Passing Parameters to Stored Procedures

Parameters are the raison d'etre of stored procedures (if you can't be pretentious in your own blog, 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

AS

-- 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

)

AS

-- 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)

)

AS

-- 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)=''

)

AS

-- 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)

)

AS

-- 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, which will - eventually - form part of this tutorial.  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.

This blog has 0 threads Add post