Home > Articles > Web Services > XML

  • Print
  • + Share This
From the author of

Executing Stored Procedures

So far, everything we've done has been either SQL queries or templates in a URL or template files accessed via a URL. There has been one glaring omission here, however: stored procedures and how we execute them. In essence, we've been mimicking stored procedures through the use of template files. Now we'll use stored procedures as they should be used. (No doubt we'll be required to know how to do this in both URL queries and template files.)

Granting users the capability to write and execute stored procedures against a database is not the most secure way of doing business. Administrators should allow the user to read and execute stored procedures written by developers but not to write files to the TemplateVirtualDirectory. You would be leaving yourself open to all sorts of problems otherwise.

Listing 4.21 gives the stored procedure we'll use throughout this discussion.

Listing 4.21 Example Stored Procedure

IF EXISTS (SELECT name FROM sysobjects
  WHERE name = 'OrderInfo' AND type = 'P')
  SELECT OrderID, CustomerID
  FROM  Orders
  WHERE CustomerID='CHOPS'

This stored procedure can be executed using this URL:


Listing 4.22 gives the result file.

Listing 4.22 Results of Calling the Example Stored Procedure

<?xml version="1.0" encoding="utf-8" ?>
 <Orders OrderID="10254" CustomerID="CHOPS" />
 <Orders OrderID="10370" CustomerID="CHOPS" />
 <Orders OrderID="10519" CustomerID="CHOPS" />
 <Orders OrderID="10731" CustomerID="CHOPS" />
 <Orders OrderID="10746" CustomerID="CHOPS" />
 <Orders OrderID="10966" CustomerID="CHOPS" />
 <Orders OrderID="11029" CustomerID="CHOPS" />
 <Orders OrderID="11041" CustomerID="CHOPS" />

Passing parameters is accomplished by utilizing the @ symbol again for the parameter expression in the stored procedure, as shown in Listing 4.23.

Listing 4.23 Passing a Parameter to a Stored Procedure

  SELECT OrderID,CustomerID
  FROM  Orders
  WHERE CustomerID=@CustomerID

The stored procedure can then be called via a URL in one of two ways. The first method is as follows:


This method provides the value CHOPS by virtue of its position. If two parameters were being passed, you could just put them one right after the other, and they would be correctly passed.

The second method is as follows:


This method provides the value CHOPS by name, which is the method we are most used to.

  • + Share This
  • 🔖 Save To Your Account