INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • 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!

*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

Jobs from Indeed

How to build string with cursor

How to build string with cursor

(OP)
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!!!

RE: How to build string with cursor

(OP)
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!!!

RE: How to build string with cursor

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.

RE: How to build string with cursor

(OP)
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!!!

RE: How to build string with cursor

Nice philhege!  Very cool.

Lonnie, why not add a # in front of philhege's table and do the whole thing in memory. :)

RE: How to build string with cursor

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

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!

Resources

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