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')
  DROP PROCEDURE OrderInfo
GO
CREATE PROCEDURE OrderInfo
AS
  SELECT OrderID, CustomerID
  FROM  Orders
  WHERE CustomerID='CHOPS'
FOR XML AUTO
GO

This stored procedure can be executed using this URL:

http://IISServer/Nwind?sql=EXECUTE+OrderInfo&root=ROOT

Listing 4.22 gives the result file.

Listing 4.22 Results of Calling the Example Stored Procedure

<?xml version="1.0" encoding="utf-8" ?>
<ROOT>
 <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" />
</ROOT>

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
  FOR XML AUTO
...

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

http://iisserver/Nwind?sql=execute+OrderInfo+CHOPS

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:

http://iisserver/Nwind?sql=execute+OrderInfo+@CustomerID=CHOPS

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

  • + Share This
  • 🔖 Save To Your Account