Executing Stored Procedures Using HTTP

Stored procedures can be executed at the URL, using either the Transact-SQL execute syntax: EXECUTE MySP; or the ODBC call syntax: {call+MySP}.

Parameters can be passed to stored procedures. Parameters are selected by taking any unused name=value pairs and supplying them as parameters to the query in the order supplied. For more information about the URL syntax that is support by the SQL ISAPI extension, see URL Access.

Examples

In the following examples, nwind is a virtual directory used to access the Northwind database. For more information about creating the nwind virtual directory, see Creating the nwind Virtual Directory.

A. Execute a simple stored procedure

This example creates and executes a stored procedure that returns a category name from the Categories table in the Northwind database. The stored procedure takes no parameters.

IF EXISTS (SELECT name FROM sysobjects
   WHERE name = 'CategoryInfo' AND type = 'P')
   DROP PROCEDURE CategoryInfo
GO
CREATE PROCEDURE CategoryInfo
AS
    SELECT CategoryName
    FROM     Categories
    FOR XML AUTO
GO

This stored procedure can be executed using a URL:

http://IISServer/nwind?sql=EXECUTE+CategoryInfo&root=ROOT

B. Execute a stored procedure with a parameter

In this example, a stored procedure with parameters is executed using a URL. The stored procedure retrieves, for a given category ID, the category name from the Category table in the Northwind database.

IF EXISTS (SELECT name FROM sysobjects
   WHERE name = 'CategoryInfoWithInputParam' AND type = 'P')
   DROP PROCEDURE CategoryInfoWithInputParam
GO

CREATE PROCEDURE CategoryInfoWithInputParam
                    @CategoryID int
AS
    SELECT '<ROOT>'
    SELECT CategoryName
    FROM     Categories
    WHERE    Categories.CategoryID = @CategoryID 
    FOR XML AUTO
    SELECT '</ROOT>'
GO

This stored procedure can be executed using a URL:

http://IISServer/nwind?sql=execute+CategoryInfoWithInputParam+1

Or

http://IISServer/nwind?sql=execute+CategoryInfoWithInputParam+@CategoryID=1

The first example specifies the parameter value (1) by position (that is, without a parameter name). The second example specifies the parameter name with the value.

C. Execute a stored procedure using the Transact-SQL EXECUTE and ODBC Call syntax

This stored procedure returns employee information for a given employee from the Employees table in the Northwind database. The stored procedure takes the employee first name and last name as input and returns the employee ID, employee title, and the birth date.

IF EXISTS (SELECT name FROM sysobjects
   WHERE name = 'FindEmp' AND type = 'P')
   DROP PROCEDURE FindEmp
GO

CREATE PROCEDURE FindEmp @FName varchar(20), @LName varchar(20) AS
SELECT    EmployeeID, Title, BirthDate
FROM       Employees
WHERE    FirstName = @FName
AND       LastName = @LName
FOR XML AUTO
GO

The Transact-SQL EXECUTE statement can be specified to execute the stored procedure:

http://IISServer/nwind?sql=SELECT+'<ROOT>';EXECUTE+FindEmp+'Nancy'+,+'Davolio';SELECT+'</ROOT>'

Or

http://IISServer/nwind?sql=SELECT+'<ROOT>';EXECUTE+FindEmp+@FName='Nancy'+,+@LName='Davolio';SELECT+'</ROOT>'

ODBC call syntax can also be specified to execute the stored procedure:

http://IISServer/nwind?sql=SELECT+'<ROOT>';{CALL+FindEmp}+'Nancy'+,+'Davolio';SELECT+'</ROOT>'

Or

http://IISServer/nwind?sql=SELECT+'<ROOT>';{CALL+FindEmp}+@FName='Nancy'+,+@LName='Davolio';SELECT+'</ROOT>'

See Also

Accessing SQL Server Using HTTP

Retrieving XML Documents Using FOR XML

Using IIS Virtual Directory Management for SQL Server Utility