Executing stored procedures

Try

exec storedproc; 
go 100

This will execute it 100 times

Oleg

<!-- Begin Edit

Here is the sample to do it with the cursor loop. I will use simple xml to store the procedure parameters, so I don't have to create a temp table:

use AdventureWorks;
go

-- sample stored procedure
create proc dbo.sample_proc 
(
    @Title nvarchar(49),
    @SalariedFlag bit
)
as

begin

select * 
    from HumanResources.Employee
    where 
        Title like @title + '%'
        and SalariedFlag = @SalariedFlag;

end;
go

Suppose I want to execute the proc few times each time with different parameters. I can store the parameter values in the xml , open the cursor reading data from it and executing the proc once per cursor loop step:

declare @Title nvarchar(49);
declare @SalariedFlag bit;

declare @xml xml;
set @xml = '<r><t>Production</t><s>0</s></r>
<r><t>Engineer</t><s>1</s></r>
<r><t>Design</t><s>1</s></r>
<r><t>Recruiter</t><s>0</s></r>
<r><t>Application</t><s>1</s></r>
<r><t>Network</t><s>1</s></r>';

declare c cursor read_only for
    select
        item.value('t[1]', 'nvarchar(49)') Title,
        item.value('s[1]', 'bit') SalariedFlag
        from @xml.nodes('//r') R(item);

open c;

fetch next from c into @Title, @SalariedFlag;

while @@fetch_status = 0
begin

    exec dbo.sample_proc @Title, @SalariedFlag;
    fetch next from c into @Title, @SalariedFlag;

end;

close c;

deallocate c;
go

End Edit -->

Hello

Here si th example I created...
1. Created a table emp_test
Create table emp_test
(eid int,
enm varchar(10),
age int)

1. Inserted one record into the emp_test table
   insert into emp_test
   (eid,enm,age) values
   (1,'AA',23)

2. Then created one stored procedure p_test
   Create proc p_test
   as
   select * from emp_test

3. The I worte one more stored procedure to execute the stored procedure p_test
   create proc p_test_run
   as
   declare @i int
   Begin
   set @i=1;
   while @i< 122
   begin
   exec p_test;
   set @i=@i+1;
   end;
   end;

It is running fine, but it gives an error message "The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid."

But if you are trying to update the data, i dont think you are going to have any problem.

Thank you Vpadmala