Browse DevX
Sign up for e-mail newsletters from DevX


The Baker's Dozen: 13 Productivity Tips for Database Development Using Transact-SQL : Page 2

This installment of "The Baker's Dozen" presents a variety of real-world database situations and how you can use Transact-SQL and SQL-92 to tackle these issues.

Tip 3: UDFs to Return a Table
Requirement: In Tip #1, you implemented a stored procedure to return multiple result sets for a single account. Now imagine that you need to expand this requirement so that users can select one account, ten accounts, or even fifty accounts, and view the order information for all these accounts.

Some developers use a combination of building an IN statement with dynamic SQL, temporary tables, and other means to address this situation. Because UDFs can return tables as well as scalar values, you can use this capability to more cleanly address the requirement for running a stored procedure for a variable number of accounts. You'll do so by modifying the stored procedure in Tip #1 to perform the following:

  • Pass the list of selected accounts as a comma-separated parameter (e.g., 1,2,17,912,1091) to the stored procedure.
  • Create a UDF (see Listing 4) that receives the comma-separated list of keys, builds a table variable with a one row integer key for each entry in the list, and returns the table variable. Call the UDF as part of the JOIN into the Orders tables to retrieve all the order information for the list of accounts.
A developer can leverage this UDF any time a query runs against a variable number of accounts, products, or other variable set of keys. Listing 5 shows a version of the stored procedure from Tip #1, modified to use the UDF.

Note that the UDF in Listing 4 contains a varchar parameter of 8,000 bytes. Assuming the comma-separated list of values represents integer key values, you should be able to pass several hundred integer keys in the list before the varchar limit of 8,000 bytes is reached. Those who require something larger (either thousands of keys, or a wider character data type) will need to look at alternate approaches, such as XML.

Tip 4: Use LIKE to Perform Test Searches
Requirement: Users of a job placement application have asked for the ability to list all candidates who match keyword searches.

The T-SQL LIKE command allows you to search for patterns within strings. For example, suppose a user wants to search for the phrase "XP" within a memo column that contains "skills include Windows XP, FrontPage, etc." Developers can use the LIKE command and the wildcard percentage character (%) to perform pattern matching.

LIKE has multiple uses, depending on the type of search you want to perform. Most searches need to check for a pattern that exists anywhere within a column. However, some searches only need to return rows where a column begins with a search pattern. Additionally, some searches may be interested in rows where a column ends with a search pattern.

   -- Search anywhere in the column
   SELECT * FROM Applicants WHERE
      Skills LIKE '%XP%'  
   -- Search where skills begins with XP
   SELECT * FROM Applicants WHERE
      Skills LIKE 'XP%'
   -- Search where skills ends with XP
   SELECT * FROM Applicants WHERE
      Skills LIKE '%XP'
Additionally, you can use the single wild-character underscore character (Name LIKE '_EVIN'),

Tip 5: A Case for CASE
Requirement: You need to produce an accounting aging report that summarizes customer account balances by standard aging brackets (balance aged 1-30 days, 31-60 days, etc.). You also need to summarize the results either by account or by balance reason.

Tip #2 demonstrated basic CASE functionality to evaluate a set of customer status codes and return a corresponding date value. Now look at another example of how you can use CASE to meet a need.

Although it's difficult to completely avoid dynamic SQL or writing multiple queries that differ only slightly, effective use of CASE can certainly reduce these instances.
Some development solutions take detail or partly-summarized result sets and perform further summarization in a business component. Although not invalid, this approach overlooks the ability to build the final result set completely in SQL 2000. For example, some solutions retrieve the accounting data from SQL and then process the results into the appropriate aging brackets. Listing 6 demonstrates each accounting balance grouped into the corresponding aging bracket. By using a CASE statement to evaluate a date column against a date range (a date column between AsOfDate-90 days and AsOfDate-61 days, etc.), the query can place the amount into the corresponding bracket.

That addresses the initial requirement to group customer accounting balances into aging brackets. You can also use the CASE statement in the GROUP BY clause to handle the need to sum the results by account or by category. Although it's difficult to completely avoid dynamic SQL or writing multiple queries that differ only slightly, effective use of CASE can certainly reduce these instances.

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