Browse DevX
Sign up for e-mail newsletters from DevX


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

SQL Server 2005 offers T-SQL language features that can improve your productivity.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

Thanks for your registration, follow us on our social networks to keep up-to-date