SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Mimic Mail Merge Functionality
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike0913
Starting Member

7 Posts

Posted - 01/27/2008 :  11:49:45  Show Profile  Reply with Quote
sdcsdcsdc

Edited by - mike0913 on 02/23/2008 14:47:09

mike0913
Starting Member

7 Posts

Posted - 01/27/2008 :  12:21:05  Show Profile  Reply with Quote
P.S. If you guys have any other ways to implement what I'm trying to implement, that would be helpful too. Thanks guys!
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 01/27/2008 :  13:29:55  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

mike0913
Starting Member

7 Posts

Posted - 01/27/2008 :  14:09:49  Show Profile  Reply with Quote
I apologize.. newbie to this forum... :D
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 01/27/2008 :  14:15:02  Show Profile  Reply with Quote
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
Go to Top of Page

mike0913
Starting Member

7 Posts

Posted - 01/27/2008 :  14:23:49  Show Profile  Reply with Quote
xssdsd

Edited by - mike0913 on 02/23/2008 14:47:34
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 01/27/2008 :  15:21:57  Show Profile  Reply with Quote
>>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
Go to Top of Page

mike0913
Starting Member

7 Posts

Posted - 01/27/2008 :  18:33:59  Show Profile  Reply with Quote
sdsdfsd

Edited by - mike0913 on 02/23/2008 14:48:02
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 01/27/2008 :  19:10:20  Show Profile  Reply with Quote
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

Edited by - TG on 01/27/2008 19:14:25
Go to Top of Page

mike0913
Starting Member

7 Posts

Posted - 01/27/2008 :  20:04:10  Show Profile  Reply with Quote
fgdfgdfg

Edited by - mike0913 on 02/23/2008 14:47:50
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 01/27/2008 :  20:37:08  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/24/2008 :  01:44:54  Show Profile  Reply with Quote
Why has OP edited all his posts? I cant understand what these people gain from doing this though!
Go to Top of Page

mike0913
Starting Member

7 Posts

Posted - 04/16/2008 :  20:19:57  Show Profile  Reply with Quote
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!



Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 05/16/2008 :  16:17:14  Show Profile  Reply with Quote
Because you're a posterior orafice.

An infinite universe is the ultimate cartesian product.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36823 Posts

Posted - 05/16/2008 :  16:21:42  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 05/16/2008 :  16:35:06  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000