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.
Author |
Topic |
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2006-01-27 : 08:35:00
|
I have a template table. One of the columns is [Content].The content of a template looks like this"Employee ##FullName## went sick at ##SickDate##.""I want to replace the tags ##FullName## and ##SickDate## with data from the database.That's easy:SELECT @Content = [Content] FROM tblTemplate WHERE uid =1Then get the columns from the SicktabelSELECT @FullName = FullName, @SickDate = SickDate FROM tblSick.SET @Content = REPLACE (@Content,'##FullName##', ISNULL (@FullName,'<no name>'))SET @Content = REPLACE (@Content,'##SickDate##', ISNULL (@SickDate,'<no date>'))RETURN @Content.However. That's static.What I want is a dynamic SQL string (made by a user with a fancy query designer) and replace the columns dynamicly (imagine the user has also a fancy tool for making templates).So if the users makes a query "SELECT * FROM tblTask WHERE uid = @uid" and a template content with "New task made ##Created## with ##Description##"That my procedure reads the columns of the query:For Each Column in resultset.columns@Content = REPLACE (@Content, '##' + ColumnName + '##, ColumnName)NextThen I have made dynamicaly a E-mail builder and a user can make whatever strikes his fancy.In VB.NET this is easy but I want to make some logic which will makes e-mails overnight.Can you help me?Henri~~~~The envious praises me unknowingly |
|
Kristen
Test
22859 Posts |
Posted - 2006-01-28 : 02:48:00
|
We do template substitution, like this, in our Application/Presentation layer because of all the things SQL Server can do this is not one of its strengths - at least in terms of efficiency/execution time and limits of string length etc.Kristen |
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2006-01-28 : 03:30:38
|
In the application layer it was indeed only 15 minutes of work.The thing is: I want to create e-mails and letters at night without losing flexibility. I have two approaches. One is writing a windows service, easy but another thing to take account with. The other is CLR integration in SQL 2005. It took me half a day to get my first example working, and I still don't know exactly what I am doing. If I have a step-by-step way found out, I'll post it.Henri~~~~The envious praises me unknowingly |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-28 : 06:48:38
|
We have a "merge" module in our Web application. We also have an EMail module in that application. So for our "batch email" we used those modules in a new "sub-application" which we schedule (Windows scheduler). It calls a single SProc that has a sniff around for the various Emails that need sending and queries the database first for the Template and then it follows with a recordset of the list of "recipients" and relevant "merge data".The application loops around the recordsets until there are no more!Dunno if that would suit your ideas, and I expect you've already thought of it, but just thought I'd mention it anyway.Kristen |
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2006-02-01 : 09:43:38
|
I got CLR integration working. So now I can use VB code to contruct mails with VB.NET at night. This makes SQL2005 perfect!(I've got a dutch blog: http://www.arcencus.nl/Default.aspx?tabid=83&EntryID=5 )Henri~~~~The envious praises me unknowingly |
|
|
|
|
|
|
|