SQL - Stored Procedure Loop

Posted on 2007-07-23
Last Modified: 2011-10-03

I'm using Miscrosoft SQL 2005.

I have a StoredProcedure. Each time I run this StoredProcedure  it takes a DateTime input:
exec MySP '2007-07-23'

...and creates a View
select ViewField from MyView

I have a query that returns me a list of DateTime fields
select MyDTField
from MyTable
order my MyDTField desc

2007-07-20 08:53:46.120
2007-07-21 00:00:00.000
2007-07-22 00:00:00.000

How can I loop throught all the datetime fields in MyTable query, and for each datetime value run the StoredProcedure MySP and insert the datetime value and the result from MyView in a table?

Foreach (datetime in MyTableQuery)
exec MySP 'datetime'      

select ViewField from MyView

insert into TmpTable(datetimeField, ViewField)
values('datetime', ViewField)

How is this possable in SQL?

Thank you.
Question by:Endelm
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    Declare @i int, @Rows int, @Dt datetime
    declare @tab table ( i int identity, Dt datetime )
    select MyDTField
    from MyTable

    SELECT @Rows = @@Rowcount, @i = 1
    WHILE @i <@Rows
     SELECT @Dt = Dt
     FROM @Tab
     WHERE i = @i

     Exec mySP @Dt

     SET @i = @i+1
    LVL 15

    Assisted Solution

    if you're returning a value from the SP to insert into the table, you should use an output parameter.

    declare @dt datetime,
    declare @vw <whatever_data_type>

    declare cur cursor for

    select MyDTField
    from MyTable
    order my MyDTField desc

    open cur
    fetch cur into @dt

    while @@fetch_status=0

    exec MySP @dt, @vw

    insert into tmptable(datetimefield,viewfield) values(@dt, @vw)
    fetch cur into @dt

    close cur
    deallocate cur
    LVL 50

    Accepted Solution

    like this

    declare @i int,@max int,@dt datetime

    select MyDTField,identity(int,1,1) as rowid
      into #temp
    from MyTable
    order my MyDTField desc

    select @i=1,@max=@@rowcount

    create table #temp1 as (identity(int,1,1) as rowid
        ,mydtfield datetime,viewfield datetime)
    while @i<=@max
       select @dt=mydtfield,@i=@i+1
         from #temp
        where rowid=@i
       Insert into #temp1 (viewfield)
         exec mysp @dt
       Update #temp1
          set mydtfield=@dt
        Where mydtfield is null


    Author Comment

    Thank you for your replies.

    I'm a little puzzled about the sql syntax in this, so I made a simple example:

    ------ Date Table ------------
    Let's pretend this is a "big" table with many records:
    create table dbo.TestTable
    (id int identity(1,1),
    date datetime,
    price numeric(30,10),

    insert into dbo.TestTable(date, price)
    values( '2007-07-22', 100)

    insert into dbo.TestTable(date, price)
    values( '2007-07-21', 200)

    insert into dbo.TestTable(date, price)
    values( '2007-07-20', 300)

    select, t.price
    from dbo.TestTable t
    order by date desc

    Gives me:
    1      2007-07-22 00:00:00.000      100.0000000000
    2      2007-07-21 00:00:00.000      200.0000000000
    3      2007-07-20 00:00:00.000      300.0000000000

    create view dbo.TestSumView as
    select sum(price) as SumPrice
    from dbo.TestTable

    --------Stored Procedure-------
    This stored procedure updates my TestSumView:
    exec MySP

    How can I loop through all my TestTable records, and for each time run this stored procedure and get the value from the view and insert it into a TmpTable?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now