Mimic Mail Merge Functionality

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Mimic Mail Merge Functionality

Author

Topic

mike0913
Starting Member

7 Posts

Posted - 2008-01-27 : 11:49:45

sdcsdcsdc

mike0913
Starting Member

7 Posts

Posted - 2008-01-27 : 12:21:05

P.S. If you guys have any other ways to implement what I'm trying to implement, that would be helpful too. Thanks guys!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-27 : 13:29:55

moved from script library.
Script library is for posting working scripts only.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

mike0913
Starting Member

7 Posts

Posted - 2008-01-27 : 14:09:49

I apologize.. newbie to this forum... :D

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-27 : 14:15:02

Perhaps something like this:


select	replace(replace(replace(LetterText, 
		'[FirstName]', a.FirstName)
		,'[ClaimNumber]', convert(varchar, a.ClaimNumber))
		,'[ServiceStartDate]', convert(varchar, ServiceStartDate, 101))
		--,...
from	testLetter tl
join	Applicant a on a.ClaimStatus = 
		case 
		when tl.lettertype = 'Rejection Letter' then 'REJECTED'
		when tl.lettertype = 'Approval Letter' then 'Approved'
		end

Be One with the Optimizer
TG

mike0913
Starting Member

7 Posts

Posted - 2008-01-27 : 14:23:49

xssdsd

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-27 : 15:21:57

>>I'm trying to avoid to having hard code everything

Well you would just need one REPLACE nesting for each replaceable column in [Applicant]. That would be six according to your DDL of applicant table. Then just one WHEN expession for each ClaimStatus. How many distinct value are there? An alternative to the CASE statement in JOIN clause would be to create a Mapping table that associates LetterType to ClaimStatus then you wouldn't need a CASE statement at all.

Be One with the Optimizer
TG

mike0913
Starting Member

7 Posts

Posted - 2008-01-27 : 18:33:59

sdsdfsd

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-27 : 19:10:20

Here is a complete example of what I was suggesting. SqlQuery column is not needed. Notice that the different letter types all work even though they have different [replacement] codes. If you want to you can select all ClaimStatus types at once.


CREATE TABLE #testLetter(
LetterID int IDENTITY(1,1) NOT NULL,
LetterType nvarchar(50),
LetterText nvarchar(4000),
SQLQuery nvarchar(3000) null
)

insert #testLetter (LetterType, LetterText)
values ('Rejection Letter'
	,'Dear [FirstName], The application you filed against our office with claim ID [ClaimNumber]' + 
	' was not approved. The provided service date [ServiceStartDate] and [ServiceEndDate] indicates' + 
	' that you are not eligible for application.')

insert #testLetter (LetterType, LetterText)
values ('Approval Letter'
	,'Dear [FirstName], Congratulations, the application you filed against our office with claim ID ' + 
	'[ClaimNumber] has been approved.')

create table #Applicant 
	(ClaimNumber int
	,Firstname varchar(20)
	,LastName varchar(20)
	,ServiceStartDate datetime
	,ServiceEndDate datetime
	,ClaimStatus varchar(20))

insert #Applicant
select	10001
	,'John'
	,'Doe'
	,'1/9/2001'
	,'6/10/2005'
	,'REJECTED'
union all
select	10002
	,'Jane'
	,'Doe'
	,'5/4/1997'
	,'12/22/1999'
	,'APPROVED'
union all
select	10003
	,'Joe'
	,'Young'
	,'11/4/2005'
	,'2/22/2006'
	,'REJECTED'

select	replace(replace(replace(replace(LetterText, 
		'[FirstName]', a.FirstName)
		,'[ClaimNumber]', convert(varchar, a.ClaimNumber))
		,'[ServiceStartDate]', convert(varchar, ServiceStartDate, 101))
		,'[ServiceEndDate]', convert(varchar, ServiceEndDate, 101))
from	#testLetter tl
join	#Applicant a on a.ClaimStatus = 
		case 
		when tl.lettertype = 'Rejection Letter' then 'REJECTED'
		when tl.lettertype = 'Approval Letter' then 'Approved'
		end

drop table #applicant
drop table #testLetter

EDIT:
I changed the datatype of a nvarchar(max) because here at home I only have sql 2000

Here is the output:
Dear John, The application you filed against our office with claim ID 10001 was not approved. The provided service date 01/09/2001 and 06/10/2005 indicates that you are not eligible for application.

Dear Joe, The application you filed against our office with claim ID 10003 was not approved. The provided service date 11/04/2005 and 02/22/2006 indicates that you are not eligible for application.

Dear Jane, Congratulations, the application you filed against our office with claim ID 10002 has been approved.

Be One with the Optimizer
TG

mike0913
Starting Member

7 Posts

Posted - 2008-01-27 : 20:04:10

fgdfgdfg

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-27 : 20:37:08

You're welcome.

I see your point but I'm still not convinced passing the query is the right way. I think it depends on the answer to two more quesitons:

How many different [replacement] values do you anticipate needing total? And how many different tables will they come from? So far you've identified 2 tables (applicant and address) with 7 possible [replacements]. It would be very easy to add [address] to the query thus making any columns from address available for replacment.

A custom query for each letterType seems to go against your desire not to "hardcode everything". Another reason I don't like the sqlQuery idea is that passing executable sql to a process raises a lot of difficult issues including: varying column counts, dynamic sql which can have security ramifications and error handling for "un-managed" sql code. So if you can avoid that I think you'll be happier.

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-24 : 01:44:54

Why has OP edited all his posts? I cant understand what these people gain from doing this though!

mike0913
Starting Member

7 Posts

Posted - 2008-04-16 : 20:19:57

Because i want to.. =)

quote:
Originally posted by visakh16

Why has OP edited all his posts? I cant understand what these people gain from doing this though!

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-16 : 16:17:14

Because you're a posterior orafice.

An infinite universe is the ultimate cartesian product.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-16 : 16:21:42

quote:
Originally posted by mike0913

Because i want to.. =)

quote:
Originally posted by visakh16

Why has OP edited all his posts? I cant understand what these people gain from doing this though!

Behavior like that could get you banned from this site.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-16 : 16:35:06

quote:
Originally posted by cat_jesus

Because you're a posterior orafice.

An infinite universe is the ultimate cartesian product.

Yes he is. Such a cheap guy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources