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 2000 Forums
 Transact-SQL (2000)
 How to dynamicaly read columns

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 =1

Then get the columns from the Sicktabel

SELECT @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)
Next

Then 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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -