Calling Stored Procedures in URLs

The SELECT statements that we have entered so far in the URL of our browser have been relatively short. But you should be able to see how they could quickly become cumbersome as we start to enter more complex queries and use joins. Also, we really don't want to have a user learn the SQL language just to display some data in a browser. This is where stored procedures can be very useful.

 

Using a stored procedure we can use the same SELECT statements that we entered into the URL of the browser and reduce the length of the URL that needs to be entered. We can then just execute the stored procedure in the URL instead of having to specify the entire SELECT statement.

 

As we have discovered in past chapters, using a stored procedure is more efficient because it is optimized in SQL Server and is compiled and cached on its first execution. This, and the fact that we can easily write more complex queries in a stored procedure, makes using them ideal.

 

When we execute a stored procedure in the URL we need to specify the EXECUTE keyword and the stored procedure name, just as we would in the Query Analyzer. An example of this is shown:

 

 

The only difference between this URL and the last is that this URL executes a stored procedure instead of the SELECT statement. All other keywords such as SQL, XSL, ContentType, and Root must still be specified.

Let's put this knowledge to use by creating a stored procedure to select the hardware data that is required by our Hardware.xsl template.

 

1.       The stored procedure that we want to create is listed below. Enter the code for this stored procedure in the Query Analyzer and execute it:

 

2.       To test this stored procedure enter the following URL in your browser:

 

The results of executing this stored procedure should be the same as you saw in the last exercise. The only difference here is that we have just executed a stored procedure in the URL instead of a SELECT statement.

How It Works – Hardware Select Stored Procedure

This stored procedure looks just about like every other SELECT stored procedure that we have created. We start the stored procedure by specifying the CREATE PROCEDURE statement followed by the stored procedure name and the AS keyword.

 

Then we specify the SELECT statement, which selects two columns from the Hardware_T table. We have also included the FOR XML clause so the results of the stored procedure will be returned as XML data to the browser:

 

 

We specify the GO command to have the Query Analyzer create this stored procedure before we grant permissions on it to the hardware users role:

 

 

Stored Procedure Parameters

Now that we know we can execute a stored procedure in the URL, it stands to reason that we could also execute a stored procedure that accepts parameters. This is true, and not as difficult as it may seem. This section will walk through a couple of examples that illustrate executing stored procedures that accept parameters, and point out what is needed to pass parameters to a stored procedure.

 

When we execute a parameterized stored procedure in the Query Analyzer, we simply specify the EXECUTE statement followed by the stored procedure name and any parameters that it might expect. Looking at the following example, the up_parmsel_assigned_system stored procedure accepts one parameter, the Employee_ID. Execution of this code produces the desired results:

 

 

Assuming this stored procedure returned the results as XML data we would execute this same stored procedure in a browser using the following code fragments in place of the SQL statements.

 

The first code fragment demonstrates executing this stored procedure by only passing the parameter as we do in the Query Analyzer:

 

 

The second code fragment demonstrates specifying the parameter name and its value. When using this method the parameter name specified must exactly match the parameter name in the stored procedure:

 

 

Let's assume for a moment that we have a stored procedure named up_parmsel_employee. This stored procedure expects the employee's last name as the first input parameter and the employee's location ID as the second input parameter. To execute this stored procedure in a URL we would specify the code as shown in the following code fragments in place of the usual SQL statements.

 

The first example simply specifies the parameter values. Notice that we have included a comma between the two input parameters and, since the first parameter is a string value, it has been enclosed in single quotes:

 

 

The second example specifies the parameter names and parameter values. Again we have enclosed the first parameter in single quotes and used a comma to separate the parameters:

 

 

Now that we know that we can execute a parameterized stored procedure in a URL we want to create a stored procedure that accepts parameters so we can experience this first hand. The stored procedure that we want to create should select most of the columns in the Hardware_T table. The input parameter to this stored procedure will be the Hardware_ID, which will point to the row of data that we want to select.

 

1.       The code for this stored procedure is listed below. Enter this code in the Query Analyzer and execute it:

 

2.       Before you execute this stored procedure in a browser, you will need to obtain a valid number for the hardware ID. You can do this by right-clicking on the Hardware_T table in the Object Browser of the Query Analyzer and choosing Open from the context menu.

3.       Once you have a valid hardware ID enter the following URL in your browser, replacing the hardware ID specified with one that is valid in your Hardware_T table:

 

You should see results similar to those shown in the next figure. Notice that we have not used an XSL stylesheet to format the data in this example, so it is just returned as XML data:

 

 

4.       You can further test this stored procedure and see the different results by substituting the @Hardware_ID parameter with different values. If you use a value that does not exist,
you will not receive an error message but just an empty XML document, as shown in the
next figure:

How It Works – Parameterized Stored Procedure

This stored procedure accepts one input parameter, the hardware ID of the row of data to be selected. The SELECT statement selects the various columns from the Hardware_T table and the CD_T table. The results are returned as XML, as indicated by the FOR XML clause. We specify the GO command to have the stored procedure created before permissions are granted on it.

 

 

 

 

 

 

We now have a couple of stored procedures that we can execute in the URL of the browser. The first stored procedure, up_select_xml_hardware, already has an XSL stylesheet that we can use to display the data. The second stored procedure, up_parmsel_xml_hardware, does not have an XSL stylesheet so let's create one for it.

 

The previous XSL stylesheets that we created have displayed the XML data in a table using columns and rows. While this is fairly standard practice, this is not required as part of creating an XSL stylesheet. We can use whatever HTML formatting we need to get the desired results. An XSL stylesheet merely provides a means to build an HTML document and to select and format the XML data. The XSL stylesheet that we will build here will not use tables. Instead we will use a variety of HTML elements to format the data for display.

 

1.       Here is the stylesheet, which you should save in the htData directory as SystemSpecs.xsl:

 

 

2.       To test this XSL stylesheet enter the following URL in your browser:

 

This URL specifies that we want to execute the up_parmsel_xml_hardware stored procedure and we have specified the @Hardware_ID input parameter and its value. We have also specified the XSL, ContentType, and Root keywords in the URL.

The results that you see should look similar to those shown in the following figure. Notice that our data is displayed in complete paragraphs and the text is formatted using the styles defined in the in-line stylesheet:

 

 

How It Works – XSL Stylesheet for Stored Procedures

We start this stylesheet as we have the others by including the standard lines of code that start every XSL stylesheet. This lets the browser know that this is an XSL stylesheet and it should parse the data as XML. We start the HTML portion of this XSL stylesheet by specifying the <HTML> and <HEAD> tags.

 

Then we define an in-line stylesheet for our HTML document. These styles will be used in our HTML to format the text. The first style that we want to define is a style for the title text that will be displayed in our HTML document. Notice that we must begin user-defined styles with a period, and we have done so here followed by the style name:

                                            

 

The second style that we have defined will be used for most of the other text in our HTML document. This style specifies the font name to be used, as indicated by the font-family property. We have also specified that the text is to be displayed using a 10 point font, as indicated by the font-size property.

 

We then end our in-line stylesheet for our HTML document and end the <HEAD> element:

 

 

We start the body of our HTML document by specifying the <BODY> tag. Then we start a paragraph in our HTML document by specifying the <P> tag. We set the Width attribute of the paragraph so that it will span across the entire HTML document.

 

Within the paragraph we specify the text that should be displayed. However, since we want this text displayed in the center of the document we have enclosed the <CENTER> element, which will cause the text to be centered. Notice that we have also specified the Class attribute of the <CENTER> element. This attribute specifies the style that should be used to format the text within the beginning and ending tags of the <CENTER> element. This is the style that we have defined in our in-line stylesheet for our HTML document.

 

We end the paragraph by specifying the </P> tag. It should be noted here that a closing tag is not required in HTML for a paragraph element. However, to conform to XML standards and to ensure that our XSL stylesheet is well-formed, all elements must include a closing tag. If you do not specify a closing tag, you will receive an error message and the data will not be displayed.

                                       

 

We want to start retrieving some XML data next so we have specified the XSL <xsl:for-each> element. We are specifying that we want to select data from the Hardware/Hardware_T elements in our XML data, as given in the select attribute:

 

 

Next, we start another paragraph in our HTML document and specify the Class attribute of the <P> element. Here we indicate that the class to be used from our in-line stylesheet is the NormalText class. The next line specifies that we want the text to be rendered in bold as we have specified the <B> element. We have also coded the Style attribute and have set the color of the text to the RGB value of #000080, which is navy.

 

Using the XSL <xsl:value-of> element we select the appropriate elements from our XML data. Notice that after the second and third <xsl:value-of> elements we have included some static text. This will cause the data from the <xsl:value-of> elements to be displayed in between our static text to form a complete sentence.

 

We then end the bold text and the paragraph by specifying the appropriate closing tags:

 

 

The next paragraph that we define also uses the NormalText style which has been specified in the Class attribute of the <P> element. We specify some static text and then specify the XSL <xsl:value-of> element to select the HardDrive_VC element from our XML data.

 

The CD_Type_CH element is a child of the CD_T element and not the Hardware_T element. Therefore we need to specify a nested <xsl:for-each> element and specify that it should select elements from the CD_T parent element. Then we specify the <xsl:value-of> element to select the CD_Type_CH element, and the closing </xsl:for-each> tag for the CD_T element:

 

 

The next paragraph uses the <xsl:value-of> element to select the elements that we need to build this paragraph. Again we have specified static text and the <xsl:value-of> elements to build a complete sentence:

 

 

 

The next two paragraphs perform the same function as the last by using the <xsl:value-of> element and static text to build complete sentences:

 

 

After all of the paragraphs have been built we specify the closing tag for the XSL <xsl:for-each> element and the closing tags for the HTML <BODY> and <HTML> elements. Then we end the XSL stylesheet by specifying the closing tags for the <xsl:template> and <xsl:stylesheet> elements: