• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2220
  • Last Modified:

SQL - Stored Procedure Loop


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.
3 Solutions
Aneesh RetnakaranDatabase AdministratorCommented:
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
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
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

EndelmAuthor Commented:
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.date, 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?

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now