Post reply

loop through all records in a database

  • September 26, 2006 at 6:59 am

    #114590

    I return from a select statement an id field, a description and an email address.

    Ideally  I would like to loop through this result ( 3 records) and call a stored procedure each time. The stored procedure will be passed the 3 fields as parmeters and send an email to the address.

    Any suggestions?

     

  • September 26, 2006 at 10:58 am

    #662534

    I would like to avoid cursors as much as possible but in your case I think its better to use cursors:

     

    Set nocount on

    Declare @id varchar(50), @name varchar(50), @email varchar(255)

    Declare my_cursor Cursor for

    SELECT [id], [name], email from tableName

    where clause

    Open my_cursor

    Fetch Next from  my_cursor INTO @id, @name, @email

    While @@FETCH_STATUS = 0

    Begin

     --Execute sp

     exec procedurename param1 = @id,param2 =@name,..

     exec master..xp_sendmail @recipients = @email,....

     Fetch Next from  my_cursor INTO @id, @name, @email

    End

    Close my_cursor

    Deallocate my_cursor

    Set nocount off

    Thanks

    Sreejith

  • September 27, 2006 at 1:58 am

    #662655

    thanks a lot sreejith, I originally tried to use a cursor but got stuck when I coulnt figure out how to assign more than one variable from it. I'll try this and let you knwo

    Cheers Dave

  • September 27, 2006 at 6:40 am

    #662691

    Or... rewrite the stored procedure to be setbased instead of RBAR.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • September 27, 2006 at 8:30 am

    #662737

    RBAR?

    Mattie

  • September 27, 2006 at 8:41 am

    #662741

    Row by agonizing row (like a cursor).

    The opposite of set based.

  • September 27, 2006 at 8:59 am

    #662748

    Remi,

    Thanks.  I understood what Jeff was trying to convey, but I never would have decoded the acronym.

    Patrice

  • September 27, 2006 at 11:23 am

    #662798

    An it's pronounced like the steel rod that but in concrete... ree-bar... (translation... steel stick in the mud )

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply