14.1.3- Using Queries and Stored Procedures

<< 14.1.2- The Structured Query Language (SQL)

Chapter14

14.2.0- Modifying Data >>

Using Queries and Stored Procedures

We've seen some examples of SQL SELECT commands that return recordsets, and UPDATE commands that change the data stored in the database. It's great to have the freedom to write commands straight into our ASP code like that, because (now we are getting a grasp of SQL) it gives us real flexibility to make any queries and changes that our applications require.

 

But sometimes these queries are rather unwieldy, and sometimes we use them very often. So wouldn't it be nice if we could write a query, give it a name, store it in the database, and then just use the query's name in our ASP code? Well, yes it would – and this functionality is already available in the form of stored procedures (as they're known in SQL Server) and queries (as they're known in Access) – they're ready-to-run SQL statements.

 

Throughout this chapter, we'll refer to them as stored procedures.

There are a few good reasons to use stored procedures:

 

• First and foremost, it's quicker to run a stored procedure because it's stored in a precompiled form on the database. This means that, unlike the SQL commands that we write in the ASP page, they don't need to be compiled before they can be executed.
• They make your code more readable (because they're effectively a shorthand).
• We can reuse the same stored procedures over and over, in many ASP pages.

 

We're not going to teach you how to write stored procedures in this book. That's really beyond the scope of this title and belongs in the realms of a book on your favorite database server. However, we will see what a simple stored procedure looks like, and we'll demonstrate how to use it within an ASP page.

Using Stored Procedures in ASP Pages

So, assuming we've written a stored procedure, how would we use it in an ASP page? Well, the code doesn't look much different to the samples you've already seen. For example, to run a stored procedure called usp_NameAndState, which returns a recordset, you could do this:

 

objCommand.ActiveConnection = strConnect

objCommand.CommandText = "usp_NameAndState"

objCommand.CommandType = adCmdStoredProc

Set objRS = objCommand.Execute

 

Notice that we've used a different CommandType value to reflect the fact that this command is in the form of a stored procedure, and not a table or a command string. Other than that, the command structure is just the same as we've seen previously. At this level, there's no difference between an Access query and a SQL Server stored procedure.

Using Parameters

You might think that using stored procedures give you less flexibility because you can't customize them as easily – but this is where the Parameters collection comes in. A parameter is the means by which we can pass a value into a stored procedure, or receive an output parameter from a stored procedure – in just the same way we pass parameters to functions. So, at design time we write a generic query in the form of a stored procedure; and then at runtime we can use its parameters to pass user-supplied information to and from it.

A Query in Microsoft Access

If you're using the Movie2000.mdb file with Microsoft Access, then you'll find that it contains a query already. It's called qryFilmsByDirector, and you can view it by opening the Movie2000.mdb file using Microsoft Access, selecting Queries from the list on the left of the following window, then qryFilmsByDirector from the list on the right, and then clicking the Design button:

This will present you with the following view. Note, in the Criteria field, the string [Director], which indicates that this query expects a parameter – and that the query will search for records whose Director field contains the value of that parameter.

 

 

 

If you wish, you can view the parameters for the query (select Parameters from the Query menu) and from section; here you are able to specify a data type for the parameter name.

 

We'll test this out in an ASP page, in a moment; first, let's look at a stored procedure in MSDE.

A Stored Procedure in MSDE

When you're working with a SQL Server/MSDE database, the traditional method for creating a stored procedure is to write the necessary SQL statements yourself, adding the parameters to the declaration. Then you have to add it to your database. In SQL Server you can add a stored procedure to your database simply by selecting New Stored Procedure at the Stored Procedures icon for your database, entering the code and clicking OK (there is also an ISQL tool available). However, with MSDE you don't get this luxury. The good news is that it is possible to use Access as a front end for creating stored procedures with MSDE. If you don't know how to do this, then we suggest you consult Appendix L, which gives you a quick tutorial.

 

If we wanted to create a stored procedure equivalent to the Access query described above, we'd write a SQL command like this:

 

CREATE PROCEDURE usp_FilmsByDirector

@Director VarChar(50)

AS

SELECT *

FROM AllMovies

WHERE Director = @Director

ORDER BY Title

This SQL Server stored procedure has the same functionality as the Access query above, so we've given it a similar name: usp_FilmsByDirector. We've already created this stored procedure for you within the Access .mdb file and within the .mdf file for MSDE users. If you are using MSDE you will have to view the stored procedure using an ADP file, which is also available from the Wrox web site (ADP is an Access front-end for MSDE databases). Again, see Appendix L for more details on ADP.

 

Here the parameter is @Director (note that, in SQL Server-based data stores, parameters are preceded by the @ sign) and is a character string of length 50.

Passing in Parameter Values

If you're writing an ASP page that uses queries or stored procedures that expect parameter values, and your code doesn't provide those parameter values, then the data provider will generate an error message telling you exactly that. If you're querying an Access database for data via an Access query, and you forget to pass the required parameter values, then you'll get an error message like this from the OLE-DB provider for Jet databases:

 

Error Type:

Microsoft JET Database Engine (0x80040E10)

Parameter Required Director has no default value.

/BegASP/accQueryParam.asp, line 30

 

Alternatively, if you're querying for data via a SQL Server-based stored procedure, and your code doesn't pass required parameter values, then the OLE-DB provider for SQL Server will tell you something like this:

 

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E10)
Procedure 'usp_FilmsByDirector' expects parameter '@Director', which was not supplied.
/BegASP/StoredProcParam.asp, line 26

 

In each case, the error message tells you quite clearly that it is expecting a parameter, and that you didn't supply one.

The Parameters Collection

So how do we pass parameters into a query that expects them? It's simple: we use the ADO Parameters collection. This is a collection of a number of ADO parameter objects – one for each of the parameters that you wish to pass into a query. The Parameter object has a number of properties that contain the parameter's name, data type, value (etc), and whether the parameter is used to supply data or to return it.

 

The Parameters collection is a collection of the ADO Command object (as you'll see if you flick back through to the beginning of Chapter 13 , to remind yourself of the ADO object model diagram). In other words, the Parameters collection (and its Parameter objects) is only accessible through the Command object. Because parameter-based stored procedures require Parameter objects, this means that you can't use ADO to call parameter-based stored procedures except through the Command object. (By contrast, it's possible to call non-parameter-based stored procedures through the methods of the Connection or Recordset object.)

Let's demonstrate a simple parameter-based query. There are a couple of examples in the following pages that do this:

 

• In the first example, we'll write an ASP page that uses the qAccess query (which is built into Movie2000.mdb database file) to ask for data from the Movie2000.mdb database.
• In the second, we'll write an ASP page that uses the usp_FilmsByDirector stored procedure (which is built into Movie2000.mdf) to query the MSDE database for the same data.

 

They're not very different – we've presented the code separately but we'll explain them both together. Choose which of these two examples you want to go with (according to whether you're using Access or MSDE) and away we go.

The Access Movie2000.mdb already contains the query called qryFilmsByDirector that we outlined above, and we'll use it in this example to fetch some data; then we'll display it in our ASP page.

 

1.    We'll need the Access database Movie2000.mdb for this example. We'll also need to check the code in our DataStore.asp SSI file, to ensure that the strConnect connection string contains the connection details for the Access database (not the MSDE database). You can do that by ensuring the appropriate line is commented out, in DataStore.asp:

 

...

'Choose one of the following two lines, and comment out the other

strDatabaseType = "Access"

'strDatabaseType = "SQLServer"

...

 

2.    In your trusty ASP editor, create a new file (which we'll call AccQueryParam.asp). Add the following code to this new file:

 

<%

Option Explicit

Dim strConnect

%>

<!-- #include file="DataStore.asp" -->

<!-- METADATA TYPE="typelib"

FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->

<HTML>

<HEAD>

<TITLE>Access Parameter Query</TITLE>

</HEAD>

<BODY>

<%

Dim objRS, objComm, objParam, strDirector

Set objComm = Server.CreateObject("ADODB.Command")

 

objComm.ActiveConnection = strConnect ' fill in the command properties

objComm.CommandText = "qryFilmsByDirector"

objComm.CommandType = adCmdStoredProc

 

' now the parameters (actually, there's only one parameter here)

Set objParam = _

objComm.CreateParameter("Required Director", adVarChar, adParamInput, 50)

objComm.Parameters.Append objParam

 

strDirector = "Quentin Tarantino" ' you can change this if you like

objComm.Parameters("Required Director") = strDirector

Set objRS = objComm.Execute ' execute the command and generate the recordset

 

Set objComm = Nothing ' don't need the Command and Parameter objects

Set objParam = Nothing ' ... so we can clean them up

 

Response.Write "<H2>Films by " & strDirector & ":</H2>"

While Not objRS.EOF ' now loop through the records

Response.Write objRS("Title") & ", directed by " & objRS("Director") & "<BR>"

objRS.MoveNext

Wend

objRS.Close ' now close and clean up

Set objRS = Nothing

%>

</BODY>

</HTML>

 

3.    Save the AccQueryParam.asp file into the \inetpub\wwwroot\BegASPFiles folder. Then use your browser to view the page:

 

Notice that this shows only the films in which the Director field is equal to Quentin Tarantino – the query has picked out all the Tarantino films stored in the AllTitles table of the database. We'll explain the structure of the code in a moment. First, we'll try another one, this time using the MSDE.

Again, the SQL Server Movie2000.mdf already has a stored procedure, named usp_FilmsByDirector, installed. We'll use it here to query the database for some specific data, and then we'll display the retrieved data in the page.

 

1.    This example uses the MSDE data source with the Movie2000.mdf database file; so check the code in our DataStore.asp SSI file to ensure that the strConnect connection string contains the connection details for the MSDE database (not the Access database). You can do that by ensuring the appropriate lines are commented out, in DataStore.asp:

 

...

'Choose one of the following two lines, and comment out the other

'strDatabaseType = "Access"

strDatabaseType = "SQLServer"

...

 

2.    Now we'll create the main ASP file. In your editor, create a new file (which we'll call StoredProcParam.asp). This ASP page should contain the following code. In fact, it's almost the same as the Access-based AccQueryParam.asp example above, so I've highlighted the lines that are different:

 

<%

Option Explicit

Dim strConnect

%>

<!-- #include file="DataStore.asp" -->

<!-- METADATA TYPE="typelib"

FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->

<HTML>

<HEAD>

<TITLE>Access Parameter Query</TITLE>

</HEAD>

<BODY>

 

<%

Dim objRS, objComm, objParam, strDirector

Set objComm = Server.CreateObject("ADODB.Command")

 

objComm.ActiveConnection = strConnect ' fill in the command properties

objComm.CommandText = "usp_FilmsByDirector"

objComm.CommandType = adCmdStoredProc

 

' now the parameters (actually, there's only one parameter here)

Set objParam = _

objComm.CreateParameter("@Director", adVarChar, adParamInput, 50)

objComm.Parameters.Append objParam

strDirector = "Quentin Tarantino" ' you can change this if you like

objComm.Parameters("@Director") = strDirector

Set objRS = objComm.Execute ' execute the command and generate the recordset

 

Set objComm = Nothing ' don't need the Command and Parameter objects

Set objParam = Nothing ' ... so we can clean them up

 

Response.Write "<H2>Films by " & strDirector & ":</H2>"

While Not objRS.EOF ' now loop through the records

Response.Write objRS("Title") & ", directed by " & objRS("Director") & "<BR>"

objRS.MoveNext

Wend

objRS.Close ' now close and clean up

Set objRS = Nothing

%>

</BODY>

</HTML>

 

3.    Save the StoredProcParam.asp file into the \inetpub\wwwroot\BegASPFiles directory and then view the page from your browser. The result should look like the previous screenshot.

 

You might like to try changing the value of the strDirector string from Quentin Tarantino to Woody Allen, just to prove that you can control the parameter value using the ASP logic – and more importantly, without amending the stored procedure itself.

How It Works

As we said before introducing the code, these two examples are very similar – the only difference being in the name of the precompiled query/stored procedure, and in the name of the parameter.

 

There's no change to the header, so we'll just skip that, and jump straight into the meat of the code. First, we declare the variants that we'll need – for the three ADO objects and a string to hold the parameter value. Then we set one of them, objComm, to point to an ADO Command object:

 

Dim objRS, objComm, objParam, strDirector

Set objComm = Server.CreateObject("ADODB.Command")

 

We don't need to set the other two ADO objects yet – that will come in a moment.

 

Now we can start to set up all the properties of our Command object. We start with the ActiveConnection, CommandText and CommandType properties (that we've met in previous examples). This is the code from the example that uses Access (the MSDE example uses extremely similar code:

 

objComm.ActiveConnection = strConnect ' fill in the command properties

objComm.CommandText = "qryFilmsByDirector"

objComm.CommandType = adCmdStoredProc

 

The ActiveConnection is set equal to the connection string found in strConnect; the CommandText is set to the name of our Access query or stored procedure; and the CommandType is set to the ADO constant adCmdStoredProc, to indicate to ADO that we're using a stored procedure.

 

The next step is to create our parameter. For each parameter we need, we'll add an ADO Parameter object to the Parameters collection. This process has two stages: in the first stage we create a standalone Parameter object and define it by applying values to its properties; and in the second step we append our Parameter object to the Parameters collection.

 

Here's the first of these two stages. We use the Command object's CreateParameter method to create a standalone Parameter object, and we give it the name objParam:

 

Set objParam = _

objComm.CreateParameter("Required Director", adVarChar, adParamInput, 50)

 

We've passed four values to the CreateParameter method – giving the parameter name (which is Required Director for Access and @Director for MSDE), its data type (adVarChar signifies a character string), the input/output type (adParamInput signifies that it's an input parameter – more on that later) and the maximum size of the value (50 characters in this case, though you can give a byte value).

 

We'll look at the CreateParameter method in more detail after we've completed this example.

 

Now we've built the parameter, we can append it to the Parameters collection by using the Command object's Append method:

 

objComm.Parameters.Append objParam

 

Next, we'll give the input parameter a value. In this case we're passing a variable that contains a character string:

 

strDirector = "Quentin Tarantino" ' you can change this if you like

objComm.Parameters("Required Director") = strDirector

 

We only need one parameter so we can now execute the query. To do that, we use the Execute method, as we've seen before. Because we're selecting data, the results of the query are returned to us in an ADO Recordset object. We'll use the variant objRS to point to that Recordset object:

 

Set objRS = objComm.Execute ' execute the command and generate the recordset

Now we've got our recordset, the job of our Command and Parameter objects is complete. We don't need them again in this example, so we'll set the variants to Nothing:

 

Set objComm = Nothing ' don't need the Command and Parameter objects

Set objParam = Nothing ' ... so we can clean them up

 

Now we'll display the contents of our recordset in the page:

 

Response.Write "<H2>Films by " & strDirector & ":</H2>"

While Not objRS.EOF ' now loop through the records

Response.Write objRS("Title") & ", directed by " & objRS("Director") & "<BR>"

objRS.MoveNext

Wend

 

Finally, we can clean up the Recordset object:

 

objRS.Close ' now close and clean up

Set objRS = Nothing

Creating Parameters

Let's look at that CreateParameter method in more detail. The full syntax for the method is as follows:

 

Set objRS = objComm.CreateParameter(Name, Type, Direction, Size, Value)

 

There are five parameters to the CreateParameter method:

 

• The first, Name, can be used to specify the name of the parameter, as defined in the query or stored procedure
• The second, Type, can be used to define the data type of the parameter value. (For a full list of these constants, see the DataTypeEnum list in the ADO constants file, adovbs.inc)
• The third, Direction, can be used to specify whether the parameter is used to send data to the procedure, or return data from the procedure, or both
• The fourth, Size, can be used to specify the maximum of the parameter's value, in terms of the length of a character string or number of bytes. This is most useful for text parameters.
• The fifth, Value, can be used to specify the value of the parameter.

 

In fact, all five of these parameters are optional. In the examples above, we specified four of them but not the fifth – choosing instead to specify the parameter's value after we'd appended the Parameter object to the Parameters collection. There are many other ways to go about this. For example, you might choose to create the Parameter object without setting any of its properties, and then set the properties in several subsequent lines of code:

 

Set objParam = objComm.CreateParameter

objParam.Name = "@Director"

objParam.Type = adVarChar

objParam.Size = 50

objParam.Direction = adParamInput

objParam.Value = "Quentin Tarantino"

 

At the other extreme, you might choose to use the Append method first, to add an 'anonymous' Parameter object to the Parameters collection, which is then created and defined within the same statement using the CreateParameter method:

 

objComm.Parameters.Append objComm.CreateParameter("@Director", _

adVarChar, adInput, 50, "Quentin Tarantino")

Output Parameters

As well as returning data (which we've seen), SQL Server stored procedures are able to return values in the form of output parameters. For example, using the Movie2000 database, suppose we wanted a count of all movies in the database that were made by a particular director.

 

One way of obtaining this value would be via a SQL stored procedure like this one:

 

CREATE PROCEDURE usp_NumberOfMovies

@Director varchar(50)

AS

SELECT COUNT(*)

FROM AllMovies

WHERE Director LIKE @Director

 

When we run this stored procedure from our code, it will return a recordset containing just one column and one row (and hence one cell). The single cell in that recordset would contain the value we're after, which in this case is 2. We could call this by using the Execute method of the Command object, and passing in the name of the director in whom we are interested.

 

But there's another way – we can cut out the need to deal with a recordset, and use an output parameter instead. For this, we'd use a slightly different stored procedure, like this:

 

CREATE PROCEDURE usp_NumberOfMovies

@Director varchar(50),

@Number int OUTPUT

AS

SELECT @Number = COUNT(*)

FROM AllMovies

WHERE Director LIKE @Director

 

Here, the second parameter is an OUTPUT parameter, which means that during the course of the stored procedure we can assign a value to that parameter, and that value will be returned to the calling program when the procedure has finished executing. Our ASP code to call this stored procedure might look something like this:

 

Dim intNumber, objComm

Set objComm = Server.CreateObject ("ADODB.Command")

objComm.ActiveConnection = strConnect

objComm.CommandText = "usp_NumberOfMovies"

objComm.CommandType = adCmdStoredProc

 

objComm.Parameters.Append objComm.CreateParameter("@Director", adVarChar, _
adParamInput, 50, "Quentin Tarantino")

objComm.Parameters.Append objComm.CreateParameter("@Number", adInteger, adParamOutput)

 

objComm.Execute

intNumber = objComm.Parameters("@Number")

 

Here, we've used the Append and CreateParameter methods twice – once to append the input parameter @Director to the Parameters collection, and once to append the output parameter @Number to the collection. Note that the order of parameters in the collection is the same as the order of parameters in the stored procedure. In particular, for the second parameter we've specified that the Direction parameter should be adParamOutput (to match the fact that the second parameter of the stored procedure is an OUTPUT parameter).

 

You can also see that, in the ASP code, we haven't specified a value for the output parameter. However, after the command has been executed the output parameter will hold a value, and this value is returned from the stored procedure.

Return Values

In addition to returning output parameter values, a stored procedure can also return a return value. This is a special kind of parameter, which does pretty much the same job as an output parameter, but it allows us to assign a data type to the value that we have returned. If you tried this with the output parameter in the example above, then if you tried to assign a type other than adInteger (which is defined within the stored procedure) you'd create an error.

 

When a stored procedure returns such a value, then it is always the first-named parameter (i.e. the 'zero parameter' – parameter lists are zero-based) in the Parameters collection. When we append a return value parameter to the Parameters collection, we distinguish it by specifying a Direction of adParamReturnValue:

 

objComm.CreateParameter("Return", adVarChar, adParamReturnValue, 8)

 

This creates a return value parameter that is a character string of eight characters. Because the return value is the first in the Parameters collection, you must append it before appending any other Parameter objects.

 

Once the command has run you can access this parameter like any other:

 

strReturn = objComm.Parameters("Return")

 

or:

 

strReturn = objComm.Parameters(0)

Asking for the Parameters

If you don't want to go to the bother of adding parameters for a parameterized query, you can use the Parameters collection's Refresh method, to tell ADO to query the data provider for the parameter details. For example:

 

objComm.Parameters.Refresh

 

ADO then contacts the data provider and updates the Parameter objects in the Parameters collection for you. You can then access a parameter by ordinal number (or name), assign a value to it and execute the command. For example:

 

objComm.Parameters.Refresh

objComm.Parameters(1).Value = "Quentin Tarantino"

 

This allows you to avoid getting bogged down by the details of the parameters. In particular, if you're working with a single Command object and a number of different stored procedures, then the Refresh method is a quick-and-clean way to set up your Parameters collection with the appropriate parameters. You can do this just before each new stored procedure call. It's also extremely useful if you are having problems in setting the correct data types and sizes. If you find that you're getting a lot of errors because your parameter settings aren't right, try using a Refresh instead and then print out the data types and values from the properties of the resulting Parameter objects.

 

The Refresh method is a useful tool, but it does come at a price in terms of performance. Each time you call the Refresh method, ADO makes a trip to the data store to extract the parameter information. The more you use it in your code, the slower your code will run. Also, you should be aware that some data sources, such as SQL Server 6.5, don't support the Refresh method. As such, it's well worth considering in a development environment, but when you come to write the code for your public web site then it's probably worthwhile using the CreateParameter and Append methods to build your parameters.

<< 14.1.2- The Structured Query Language (SQL)

Chapter14

14.2.0- Modifying Data >>