Using Sql:nullvalue to Pass NULL Parameters to Stored Procedures

How do I use an XML template to pass a variable number of parameters to a stored procedure? As a workaround, I tried using NULL values, but I can't seem to pass a NULL to my stored procedure. What's happening?

XML templates in SQL Server 2000 include a mechanism for declaring and using a set of named parameters. Parameters are declared within a header element, and each parameter declaration provides the name and default value for the parameter. The default value is used for the parameter if no value is passed to the template when it is executed (either by passing the parameter on a URL with the form param1=value or using the ADO/OLE DB parameter collection). You use parameter values in SQL queries within a template by referencing the name of the parameter. You refer to the parameter by prepending the at (@) character to the parameter name. A SQL query within a template can include or exclude any of the declared parameters as required, letting you pass a variable set of parameters to the template. But although a query can choose to use any combination of the parameters declared in the template, it can't conditionally include parameters in a query based on whether the parameter was passed to the template or on the value of the parameter.

The workaround you mention in your question—passing NULL for unset parameters—is an appropriate solution to the problem you described. But you can make the solution even more effective by incorporating the sql:nullvalue feature, which was introduced to templates in XML for SQL Server 2000 Web Release 2 (WR2—you can download WR2 from Microsoft at http://www.microsoft.com/sql/downloads/default.asp). Sql:nullvalue lets the template author specify an arbitrary string that's equivalent to NULL when the string is the value of a parameter. Think of sql:nullvalue as a proxy NULL. Typically, you can use the string NULL for a proxy NULL, unless the NULL string is a valid value for the parameter. In that case, choose a string value that won't occur as a parameter value. Because the string is arbitrary, you can easily select such a value. As the template processor builds the collection of parameters that it will pass to the SQL Server query processor, the template processor merges the default values for the parameters with any values specified when the template was executed into a parameters collection. As the template processor builds the collection, it converts to NULL any parameter values equivalent to the proxy NULL value, regardless of whether the values were passed as parameters or were declared as the default values inside the template.

Listing 1 shows an example of an XML template that uses the sql:header and sql:param elements to declare a set of parameters. The sql:nullvalue attribute on the sql:header element defines the special string myNULL as the proxy NULL for this template. Inside the sql:query element, the template calls a stored procedure named showParams, passing the three declared parameters. Two of the parameters, p2 and p3, use the proxy NULL as their default value. The other parameter, p1, uses the empty string for its default value. Distinguishing between the empty string (a character string with no characters) and the NULLvalue, which signifies the absence of a value, is important. When the template processor executes the template, p2 and p3 are passed NULL unless values other than myNULL were specified for the parameters when the template was executed. For example, executing the template by using the following URL

http://localhost/December/template/template-param.xml?p1=myp1value

would pass NULL for p2 and p3, whereas the URL

http://localhost/December/template/template-param.xml?p1=myp1value&p2=myp2value&p3=myNULL

would pass only the p3 parameter as NULL. Listing 2, page 66, shows the T-SQL code for a simple stored procedure that checks for NULL parameter values and generates XML elements for parameters that aren't NULL. You can install the stored procedure in your database, then experiment with the template in Listing 1. Begin by using Query Analyzer to install the stored procedure in a database on your system. Then, using the Configure IIS Support utility installed with WR2, create a virtual root called December on your system and configure it to the database where you created the stored procedure from Listing 2. Include a virtual name called template on the virtual root with type "template" and the path '.'. Remember to enable template queries on the Settings tab. After you've completed these tasks, you can execute the template by using the previous URLs.