Presents your  
SQL SERVER E-NEWSLETTER for October 7, 2003 
 
<-------------------------------------------> 
  
DELIVER TEXT FROM SQL BY USING SELECT VALUES   
  
Front-end applications commonly run into problems when delivering the 
database equivalent of mail merge. A body of text must be delivered that
embeds various values retrieved by a SELECT statement. You can approach 
this in several ways, but perhaps the most common method is to write 
front-end code. This tip proposes another solution, which I find more
useful and easier to understand.   
  
Let's say our example is a form letter that has a Date, From and To 
addresses, and a body of text into which you must plug various values.  

  
You can use SELECT values without referring to any table, as in:   
  
SELECT 'This is some simple text'   
  
This is how SQL responds:   
  
This is some simple text   
  
You can expand this technique substantially. For example, using the 
Northwind sample database, the following query:   
  
select convert(varchar(20), getdate(),101) + Char(13) + Char(10) + 
'Dear

' +   
ContactName + ':' + Char(13) + Char(10) + ContactTitle + Char(13) + 
Char(10) +   
'We wish to inform you that you have won a fabulous hockey stick. ' 
From Customers Will produce the following output:   
  
09/01/2003   
Dear Maria Anders:   
Sales Representative   
We wish to inform you that you have won a fabulous hockey stick.   
  
There are report writers and applications, such as Microsoft Access or 
Word, that can perform essentially the same task; however, they aren't 
always appropriate.   
  
For example, you can easily use the technique this tip describes to 
generate e-mail. In addition, if you work in SQL rather than in the 
application code, you can turn it into a stored procedure, which can be
called from any application.   
  
Arthur Fuller has been developing database applications for 20 years. 
His experience includes Access ADPs, Microsoft SQL 2000, MySQL, and .NET.   
  
----------------------------------------