x

Executing stored procedures

ALL,

I have a question ,with respect to executing the stored procedures. can we execute a single stored procedure multiple times like

   exec storedproc 
   go
   exec storedproc
   go
   exec storedproc
   go
   exec storedproc
   go
   exec storedproc
      .
      .
      .

say about a 100 times in a single script

If yes... 1) is there a limit that the procedure could be called in the script

2) what is the efficient way to do it. I am thinking of writing another stored procedure which executes this procedure required number of times.

I am not sure how to handle this situation. Any advice would be very helpful.

Thanks

more ▼

asked Aug 23, 2010 at 09:59 AM in Default

Katie 1 gravatar image

Katie 1
1.4k 132 163 202

Why does the procedure need to be executed a set number of times? Isn't there a method for simply doing the set of operations required?
Aug 23, 2010 at 10:31 AM Grant Fritchey ♦♦

Grant, this procedure was designed to insert related data into various different(around 10) tables.

I wanted to use this stored procedure to insert around hundred records as part of data import. I was trying to avoid execution 100 times manually executing the proc to insert the data. So was thinking to automate it.
Aug 23, 2010 at 10:43 AM Katie 1
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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 -->
more ▼

answered Aug 23, 2010 at 10:19 AM

Oleg gravatar image

Oleg
15.9k 2 4 24

That's the one I would have chosen.
Aug 23, 2010 at 10:30 AM Grant Fritchey ♦♦
Oleg, would it apply, with the same stored procedure, with different parameters exec storedproc (parameter set 1) exec storedproc (parameter set 2) ... 100 times? etc?
Aug 23, 2010 at 10:34 AM Katie 1
@Katie No, it will not. If you need to call it with different parameters then there is no way to do it in the loop (**go n** is just a shortcut to execute the batch n times). There is a way to do it with the cursor loop if you first create a small temp table and insert the parameters into it as records (one record for all parameters). Then you can create a cursor loop and execute the proc with different set of parameters. I will add a small sample to my answer in few minutes.
Aug 23, 2010 at 11:03 AM Oleg

Thats a great idea Oleg.I will try to implement this idea.

Thank you so much.
Aug 23, 2010 at 11:18 AM Katie 1
@Katie I added a small sample to my answer.
Aug 23, 2010 at 11:28 AM Oleg
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Aug 23, 2010 at 10:20 AM

V Padmala gravatar image

V Padmala
83 2 3 4

This will work for executing the proc multiple times with same parameters, but then again why write a while loop when go n just does it. With different parameters for each execution, the regular loop cannot unfortunately help though cursor loop can.
Aug 23, 2010 at 11:33 AM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1850
x415
x17

asked: Aug 23, 2010 at 09:59 AM

Seen: 3996 times

Last Updated: Aug 23, 2010 at 09:59 AM