Running T-SQL on a Select Statment

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)

Running T-SQL on a Select Statment

Author

Topic

thedryden
Starting Member

23 Posts

Posted - 2008-02-01 : 11:40:13

I am new to T-SQL so bear with me ;).

I have created a collection of tables to work with some T-SQL to send out email via SQL Sever 2005's Database Mail. That works great. Only now I want to create a report that displays some information about the Email system so everyone can keep track of what is available to make creating new e-mails and editing existing emails easier.

My problem is as a part of the code used to send the emails a REPLACE statement is run a number of times on the Body of the email to replace "File Tag" (such as <FILE 1>) in the HTML for the e-mail with information from one of other tables in the database. While I can get this to work just fine when I'm only looking at one email at a time (see code below), I have no idea how to even start to spit out the formatted body text in a select statement so that I can put it into a report.

Can anyone help?

Here’s the code the T-SQL that send out the e-mails uses to format the body and subject text:

    DECLARE FILE_NAMES cursor for
        SELECT FILETAG
        FROM CTS.dbo.EMAIL_FILES
        ORDER BY FILEID

    SELECT @BodyText = BODY, @SubjectText = SUBJECT
    FROM CTS.dbo.EMAIL
    WHERE EMAIL_ID = @EmailID

    SET @FileTag = ''
    SET @Count = 1

    OPEN FILE_NAMES
    FETCH NEXT FROM FILE_NAMES
    INTO @FileTag

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @BodyText = REPLACE( @BodyText, '<FILE ' + cast( @Count as varchar(3) ) + '>', @FileTag )
        SET @SubjectText = REPLACE( @SubjectText, '<FILE ' + cast( @Count as varchar(3) ) + '>', @FileTag )
        SET @Count = @Count + 1

        FETCH NEXT FROM FILE_NAMES
        INTO @FileTag
    END
    CLOSE FILE_NAMES
    DEALLOCATE FILE_NAMES

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-01 : 12:07:31

Are you sure that this part of the code is in the right place?:
SELECT @BodyText = BODY, @SubjectText = SUBJECT
FROM CTS.dbo.EMAIL
WHERE EMAIL_ID = @EmailID

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

thedryden
Starting Member

23 Posts

Posted - 2008-02-01 : 12:14:01

Well I haven't had any problems with it. The entire code for send out my e-mails is below. However this is NOT the code I'm having problems with, rather I'm trying to write some kind of select or select like statement that will allow me to pull all of the BODY information formated with with the File Tags replaced.

DECLARE @EmailID as smallint
    , @BodyText as varchar(4000)
    , @SubjectText as varchar(1000)
    , @FileTag as varchar(1000)
	, @Distro nvarchar(1000)
	, @CUID as varchar(55)
    , @Count as smallint

--_________________________________________
--Loop through all emails

DECLARE EMAILC cursor for
SELECT EMAIL_ID
FROM CTS.dbo.EMAIL
WHERE CATEGORY = 'PERIODIC'

OPEN EMAILC
FETCH NEXT FROM EMAILC
INTO @EmailID

WHILE @@FETCH_STATUS = 0
BEGIN

    --_________________________________________
    --Set @BodyText and @SubjectText
    DECLARE FILE_NAMES cursor for
        SELECT FILETAG
        FROM CTS.dbo.EMAIL_FILES
        ORDER BY FILEID

    SELECT @BodyText = BODY, @SubjectText = SUBJECT
    FROM CTS.dbo.EMAIL
    WHERE EMAIL_ID = @EmailID

    SET @FileTag = ''
    SET @Count = 1

    OPEN FILE_NAMES
    FETCH NEXT FROM FILE_NAMES
    INTO @FileTag

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @BodyText = REPLACE( @BodyText, '<FILE ' + cast( @Count as varchar(3) ) + '>', @FileTag )
        SET @SubjectText = REPLACE( @SubjectText, '<FILE ' + cast( @Count as varchar(3) ) + '>', @FileTag )
        SET @Count = @Count + 1

        FETCH NEXT FROM FILE_NAMES
        INTO @FileTag
    END
    CLOSE FILE_NAMES
    DEALLOCATE FILE_NAMES

    --_________________________________________
    --Set @CUID
    DECLARE CUID_DISTRO cursor for
        SELECT r.CUID
        FROM CTS.dbo.EMAIL_RECIPIENTS r
        INNER JOIN CTS.dbo.EMAIL_DISTRO d ON r.USER_ID=d.USER_ID
        WHERE d.EMAIL_ID=@EmailID

    SET @Distro = ''

    OPEN CUID_DISTRO
    FETCH NEXT FROM CUID_DISTRO
    INTO @CUID

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @CUID LIKE '%@%'
            SET @Distro = @Distro + @CUID
        ELSE
            SET @Distro = @Distro + @CUID + '@att.com;'

        FETCH NEXT FROM CUID_DISTRO
        INTO @CUID
    END
    CLOSE CUID_DISTRO
    DEALLOCATE CUID_DISTRO

    --_________________________________________
    --Send out the mail
    EXEC msdb.dbo.sp_send_dbmail @recipients=@Distro,
        @profile_name='Metrics',
        @Subject = @SubjectText,
        @Body = @BodyText,
        @Body_format = 'HTML';

FETCH NEXT FROM EMAILC
INTO @EmailID

END
CLOSE EMAILC
DEALLOCATE EMAILC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources