Code Generation: Using SQL to Generate SQL and Other Code

SQL Server is a great platform against which to develop, but it can also be used as a code generation tool with a little creativity. Haidong Ji brings us a few techniques that can be used to automatically build T-SQL code and bcp code for importing and exporting data.

Using a Subquery in a T-SQL Statement

Sometimes the criteria for determining which set of records will be affected by a SELECT, UPDATE, DELETE and/or INSERT statement cannot be obtained by hard coding the selection criteria. Occasionally there is a need to use the results of a SELECT statement to help determine which records are returned or are affected by a T-SQL statement. When a SELECT statement is used inside another statement, the inside SELECT statement is known as a subquery. Subqueries can help to dynamically control the records affected by an UPDATE, DELETE or INSERT statement, as well as to help determine the records that will be returned by a SELECT statement. This article will show different examples of how to use a subquery to help clarify the records affected or selected by a T-SQL statement.

Optimizer Join Methods

Understanding the different types of joins used by the optimizer will help developers and DBAs understand how the optimizer is routing their queries. Developers often create queries without knowing that it would only take a few tweaks to produce an execution plan that utilizes one optimizer join method over another. These small tweaks can have dramatic effects on the optimization of the query and the ultimate satisfaction of the query by the end-users.

Generating a Bar Code

SQL Server is asked to do many tasks, including scheduling processes, transferring data using DTS in an ETL process, sending mail, updating other systems, generating reports and more. However this one might a bit unsual: using SQL Server and T-SQL to generate a bar code. Ivaca Masar brings us this unique look at how you can stretch the limits of T-SQL.

On the Trail of the ISO Week

Chris Hedgate, one of our regular SQL Server columnists, recently became intrigued by the way that SQL Server implements the week number. His concern was why SQL Server treats week 53 as week 1, which is not that way that ISO6801 sees it. Read about his investigation into the methods of implementing an algorithm to solve this problem.

Can You Compute?

Transact-SQL in SQL Server 2000 has some interesting features, many of which most DBAs will never use. While many DBAs are famliar with the basic aggregate functions, there are a few that are advanced and not well understood. The ROLLUP and COMPUTE operators are two of these and David Poole takes a look at how these work and a practical application for them.

Calculating Work Days

How many times have you wished that there was a parameter for DATEPART to allow you to check for workdays? While many of us may get stuck working 6 or 7 days a week, most of the world revolves on a 5 day work week. Monday through Friday. New author Jeff Moden brings us a method of easily calculating the number of workdays between any two dates.

Working with Datetime

Datetime data in SQL Server can be a little confusing to work with, especially as many front end languages do not combine the date and time into a single datatype. As a result, T-SQL is sometimes used to manipulate and convert datetime data into more useable formats. Author Leo Peysakhovich has written about some tricks that he uses to reformat and work with datetime data.

Evaluating Boolean expressions using T-SQL

Every once in awhile there is a unique T-SQL solution in SQL Server that solves a rare problem. Not many of us have had to deal with boolean evaluation in our jobs, but a few have. Author Eli Leiba has and brings us a new article that shows how to build a procedure that can take a boolean expression and evaluate it to true or false.

Cursors with SQL 2000 Part 2