IF (OBJECT_ID ('dbo.GetAuthorTitles') IS NOT NULL)
	DROP PROCEDURE dbo.GetAuthorTitles
GO
CREATE PROCEDURE dbo.GetAuthorTitles AS
BEGIN
	SELECT
		a.au_lname + ', ' + a.au_fname AS author,
		t.title, t.type
	FROM
		authors a
			INNER JOIN titleauthor ta ON a.au_id = ta.au_id
			INNER JOIN titles t ON t.title_id = ta.title_id
	FOR XML AUTO, ELEMENTS
END

This procedure gets all the authors and their associated titles. Let's now see how to call this stored procedure from a URL.

http://localhost/pubs?sql=EXECUTE%20GetAuthorTitles&root=root

Here "pubs" is the name of virtual folder that we created using the IIS Configuration Manager of SQLXML. Once you execute this URL, your output will be like the following (the output has been cut-off to reduce space and size)...

Note how we execute the stored procedure using the EXECUTE command. Within the procedure we specify that we want the output as XML elements and in the URL we specify that the entire XML has to be surrounded by a "root" tag.

OK. That was pretty simple... Now, let's modify the above procedure so that it takes a parameter, which indicates the type of books that we want to display. Here is the modified procedure.

IF (OBJECT_ID ('dbo.GetAuthorTitles') IS NOT NULL)
	DROP PROCEDURE dbo.GetAuthorTitles
GO
CREATE PROCEDURE dbo.GetAuthorTitles
(
	@bookType	VARCHAR(100)
) AS
BEGIN
	SELECT
		a.au_lname + ', ' + a.au_fname AS author,
		t.title, t.type
	FROM
		authors a
			INNER JOIN titleauthor ta ON a.au_id = ta.au_id
			INNER JOIN titles t ON t.title_id = ta.title_id
	WHERE
		UPPER(t.type) = UPPER(@bookType)
	FOR XML AUTO, ELEMENTS
END

Ok. So, how do we call this stored procedure now?? Here is an example.

http://localhost/pubs?sql=EXECUTE%20GetAuthorTitles%20@bookType=mod_cook&root=root

In the URL, we specify the name of the parameter and the value, after the name of the stored procedure. This is very much like how we call stored procedures from an application. Another way to call stored procedures with parameters is shown below:

http://localhost/pubs?sql=EXECUTE%20GetAuthorTitles%20business&root=root

In this case, we do not specify the name of the parameter, but just provide the value. This method is more prone to errors, since if the order of the parameters changes later on, you code might give unpredictable results. A better option is use the name of the parameter and its value.