asked
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
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?
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.
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 = 'Production0 Engineer1 Design1 Recruiter0 Application1 Network1'; 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** \-->
@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.
Hello Here si th example I created... 1. Created a table emp_test Create table emp_test (eid int, enm varchar(10), age int) 2. Inserted one record into the emp_test table insert into emp_test (eid,enm,age) values (1,'AA',23) 3. Then created one stored procedure p_test Create proc p_test as select * from emp_test 4. 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
