XML and SQL Server 2000

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.

Related

Категории