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.