The Baker's Dozen: 13 Productivity Tips for Transact-SQL 2005 : Page 4

Tip 7: XML and XQUERY
Scenario: You want to utilize XML to query against a variable number of key selections that you pass as parameters.

A common challenge I've faced in querying is supporting multiple/variable selections. A user might want to run a report for one account, or ten, or a hundred, or more. Since this might occur in several different places in an application, I've always searched for a reusable methodology.

Prior to SQL Server 2000, you generally had two choices:

• Pass a CSV list of select keys and convert the CSV list to a table variable/temp table.
• Pass an XML string and use the system stored procedure sp_xml_preparedocument and the OPENXML function.

The XML features in SQL Server 2005 are especially helpful in processing variable-length lists of keys

Either of these two options still works in SQL 2005, and the CSV approach benefits from the fact that a VARCHAR parameter can now be much larger (i.e., VARCHAR(MAX)). However, the new XML capabilities in SQL 2005 allow you to pass an XML string of selections, without the overhead of sp_xml_preparedocument and OPENXML. You can do this by using the new XQUERY capabilities in SQL Server 2005 to "shred" XML strings to read individual nodes.

Here's a table-valued UDF called XML2TableVar that you can use to convert any XML string of integer key values to a table variable:

   ALTER FUNCTION [dbo].[XML2TableVar]
   (  @XMLString XML)
   RETURNS
      @tPKList TABLE  ( PKValue int )
   AS
   BEGIN
   INSERT INTO @tPKList
   SELECT Tbl.Col.value('.','int')
   FROM   @XMLString.nodes('//PKValue') Tbl(Col)
   RETURN
   END

Listing 6 demonstrates the use of this UDF. You simply need to define an XML string with a column called PKValue:

   DECLARE @cVendorXML XML 
   SET @cVendorXML ='<Vendors>
   <Vendor>
   <PKValue>73</PKValue>
   <PKValue>76</PKValue>
   </Vendor>
   </Vendors>'

Then you can query a table and JOIN the results of XML2TableVar:

   SELECT Orders.* FROM
    Purchasing.PurchaseOrderHeader Orders
       JOIN dbo.XML2TableVar(@cVendorXML) VendorList 
   ON VendorList.PKValue = Orders.VendorID

One additional point: The first query in Listing 6 basically requires that the XML string must contain at least one entry. Suppose the user selects "all vendors" in the application, and the system contains thousands of vendors. Does the application need to deal with an XML string that contains a row for each Vendor PK?

Well, it shouldn't have to. You can modify the query in Listing 6 (the code appears at the bottom of Listing 6) to use an OUTER APPLY instead of a JOIN, and add a CASE statement to examine if the XML string is empty before deciding to use the WHERE clause on the VendorID:

   SELECT Orders.* FROM 
      Purchasing.PurchaseOrderHeader Orders
   -- Must use OUTER APPLY and a CASE statement
      OUTER APPLY dbo.XML2TableVar(@cVendorXML) 
          AS VendorList  
   WHERE
   CASE WHEN LEN(
       CAST(@cVendorXML AS VARCHAR(MAX)))
       = 0 THEN 1
   WHEN VendorList.PKValue = Orders.VendorID 
   THEN 1
   ELSE 0 END = 1

Tip 8: Variable TOP N
Scenario: You want to query against the first N number of rows based on some condition, and where N is variable. While you could do this by constructing a dynamic SQL statement, you're looking for an easier way. Additionally, you're also looking for a way to INSERT or UPDATE a variable number of rows, based on TOP N syntax.

Prior to SQL Server 2005, if you wanted to retrieve the first N number of records based on some order, and the N was variable, you either had to use dynamic SQL to construct the query or use dynamic SQL for the ROWCOUNT statement. This was because SQL 2000 treated TOP N as a literal, not a variable.

SQL Server 2005 now treats the N as a true variable. Listing 7 shows several examples of the new TOP N capabilities, including the ability to set a variable for TOP N in an INSERT/UPDATE statement.

Tip 9: APPLY and Table-Valued UDFs
Scenario: You previously realized that you and your development team were repeating logic for result sets, and decided to abstract that logic to create table-valued user-defined functions (UDFs). However, you're finding that integrating your table-valued UDFs from your queries sometimes means extra steps and extra queries. You want to call your UDFs cleanly from your queries.

Just as a picture is worth a thousand words, code samples are sometimes worth a thousand words as well. SQL Server 2005's APPLY capability allows developers to apply (once again, hence the name) reusable table-valued UDFs with queries.

If you ever tried to incorporate table-valued UDFs into stored procedures in SQL 2000, you know that you sometimes had to create additional temporary tables or additional SQL statements. Ideally, you'd like to integrate a table-valued UDF into a query, and pass a column from the main query table as a parameter into the UDF. Here's one scenario that creates table-valued UDFs and then uses a query to APPLY the UDFs:

   -- Create a Table-valued UDF
   CREATE FUNCTION [dbo].[GetTopNPurchaseOrders]
      (@EmployeeID AS int, @nTop AS INT)
   RETURNS TABLE
   AS
   RETURN
   SELECT TOP(@nTop) PurchaseOrderID, EmployeeID, 
                 OrderDate,  TotalDue
   FROM Purchasing.PurchaseOrderHeader
   WHERE EmployeeID = @EmployeeID
   ORDER BY TotalDue DESC
   GO
   
   -- APPLY the table-valued UDF
   USE AdventureWorks
   GO
   DECLARE @nTopCount int
   SET @nTopCount = 5  
   SELECT TOPOrder.EmployeeID , TOPOrder.PurchaseOrderID,  
      TOPOrder.OrderDate, TOPOrder.TotalDue
   FROM HumanResources.Employee Employee
      CROSS APPLY dbo.GetTopNPurchaseOrders
      (Employee.EmployeeID ,@nTopCount) AS TOPOrder
   ORDER BY TOPOrder.EmployeeID, TOPOrder.TotalDue DESC

Here's a second scenario:

   -- Another table-valued UDF
   CREATE FUNCTION [dbo].[GetPurchaseOrdersGTx]
      (@EmployeeID AS int, @nThreshold AS INT)
   RETURNS @tOrders TABLE (PurchaseOrderID int, 
      EmployeeID int, OrderDate datetime, 
      TotalDue decimal(14,2))
   AS
   BEGIN
   INSERT INTO @tOrders
   SELECT PurchaseOrderID, EmployeeID, 
       OrderDate,  TotalDue
   FROM Purchasing.PurchaseOrderHeader
   WHERE EmployeeID = @EmployeeID AND 
       TotalDue  >= @nThreshold  
   ORDER BY TotalDue DESC
   RETURN
   END
   GO
   
   
   --APPLY the UDF from a sub-query
   DECLARE @nNumOrders int, @nMinAmt decimal(14,2)
   SET @nNumOrders = 10
   SET @nMinAmt = 75000.00
   -- Find Employees with 2 orders at least 5K
   SELECT EmployeeID  FROM HumanResources.Employee
   WHERE  (SELECT COUNT(*) FROM 
        dbo.GetPurchaseOrdersGTx (
        EmployeeID, @nMinAmt)) >=@nNumOrders
   GO
   -- Now, for these 10 employees with 
   -- orders greater than 75,000,
   -- retrieve those orders
   DECLARE @nNumOrders int, @nMinAmt decimal(14,2)
   SET @nNumOrders = 10
   SET @nMinAmt = 75000.00
   -- use a CTE to get the list of employees
   ;WITH EmpList AS (SELECT EmployeeID  
       FROM HumanResources.Employee
   WHERE  (SELECT COUNT(*) FROM 
        dbo.GetPurchaseOrdersGTx (
        EmployeeID,@nMinAmt)) >=@nNumOrders)
   -- then query against that list
   SELECT MaxOrders.* FROM EmpList
      CROSS APPLY dbo.GetPurchaseOrdersGTx
          (EmployeeID ,@nMinAmt) 
   AS MaxOrders

Note that both the scenarios above use the CROSS APPLY statement to apply the table-valued UDF across every row in the main table.

Previous Page

Next Page