Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

LonnieJohnson (Programmer) (OP)
8 Sep 09 18:54
I want to go through a recordset and build a string (actually an html table) to send in an email.

This is what I have but the part that sets @RECORDS returns Nulls. Even though you do not see it @RECORDS is declared earlier in another part of the code.

CODE

    --BEGIN RECORDSET ITERATION TO DETERMINE WHICH RECORDS TO EMAIL ***********
    DECLARE @STEP VARCHAR(100)
    DECLARE @SCHEDULE VARCHAR(10)

    DECLARE CURSOR1 CURSOR FOR SELECT STEP AS 'JOB NAME', SCHEDULE
    FROM CLIENT_KS.DBO.WORKFLOW
    
    OPEN CURSOR1
    FETCH CURSOR1 INTO @STEP, @SCHEDULE

    While @@Fetch_Status = 0
    BEGIN

    SET @RECORDS = @RECORDS + '<TR>
                                <TD>' +
                                    @STEP +
                                '</TD>
                                <TD>' +
                                    @SCHEDULE +
                                '</TD>
                            </TR>'

    Fetch Next FROM CURSOR1 INTO @STEP, @SCHEDULE
    End

    CLOSE CURSOR1
    DEALLOCATE CURSOR1    
    --END RECORDSET ITERATION *************************************************

    SET @HTML =
    '<BR><BR>
    <TABLE BORDER=2>' +
        @RECORDS +
    '<TABLE>'

ProDev, Builders of Affordable Software Applications
Visit me at ==> http://www.prodev.us

May God bless you beyond your imagination!!!

LonnieJohnson (Programmer) (OP)
8 Sep 09 19:34
I have my own answer. I used the SELECT statement to set the value and that worked.

ProDev, Builders of Affordable Software Applications
Visit me at ==> http://www.prodev.us

May God bless you beyond your imagination!!!

Helpful Member!  philhege (Programmer)
8 Sep 09 21:41
Hi Lonnie,

There has been a lot of conversation in this forum about cursors and set-based thinking.  I'd like to pose an answer to your issue that doesn't use a cursor.

CODE

drop table sch_step
create table sch_step (pk int,step varchar(32),schedule varchar(32))

INSERT INTO sch_step
SELECT 1,'First Step','Daily'
UNION
SELECT 2,'Second Step','Daily'
UNION
SELECT 3,'Third Step','Weekly'
UNION
SELECT 4,'Fourth Step','Monthly'
UNION
SELECT 5,'Fifth Step','Daily'

declare @Records varchar(max)
set @records = '<table>'
    SELECT @RECORDS = @RECORDS + '<TR><TD>' +
                                    step + '</TD>
                                <TD>' +  schedule +
                                '</TD></TR>'
FROM sch_step
ORDER BY pk
set @records = @records + '</table>'
print @records

Pretty easy, and it makes use of SQL's set-based strengths.

HTH,

 

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

LonnieJohnson (Programmer) (OP)
8 Sep 09 22:54
OK!!!
That was totally cool.
I have never used that. I am more of an application programmer than sql programmer. Thanks.

ProDev, Builders of Affordable Software Applications
Visit me at ==> http://www.prodev.us

May God bless you beyond your imagination!!!

bigfoot (Programmer)
9 Sep 09 11:31
Nice philhege!  Very cool.

Lonnie, why not add a # in front of philhege's table and do the whole thing in memory. :)
gmmastros (Programmer)
9 Sep 09 11:47
I would probably NOT build HTML in a stored procedure.  Instead, I would return XML (from the database) and then use XSLT to transform it in to HTML (for display on a web page).  This allows you to separate your data from how it is displayed.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close