Execute Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Execute Procedure

Author

Topic

sanjay5219
Posting Yak Master

240 Posts

Posted - 2012-08-23 : 12:47:18

Hi All,

I have one procedure and has to get executed based on one table and empidhas to come from there.

Select empid from Table1

Exec Build_Hierarchy @empid

so if Table1 is having 10 rows then this procedure should get executed 10 times with difreent empid

please suggest

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-23 : 12:51:39

You will need to do this either in a while loop, once for each value in Table1. You could conceivably build a dynamic SQL query - but that is fraught with peril as well.

A better option - if at all possible - would perhaps be to open up the code for the Build_Hierarchy stored proc and change it to build the hierarchy for all employees (or a select group of employees) in one set-based query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-23 : 13:06:21

why not make it into user defined function then?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sanjay5219
Posting Yak Master

240 Posts

Posted - 2012-08-23 : 13:10:04

i was planning to have put this in loop but i have never worked on loop

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-23 : 13:57:15

Here is an example. I am creating a stored proc and using it in the loop. Just be careful to have your exit condition tested properly to avoid the query running in an endless loop.

Also, consider what Visakh suggested - if you are able to make the stored proc into a function, you can join on the table. But not all queries can be converted to functions because of limitations and restrictions of functions. But, like I originally suggested, if you are going to open up the stored proc, you may have other options as well.

CREATE PROCEDURE dbo.TestProc
	@x INT
AS 
	SELECT @x;
GO
----------------------------
DECLARE @y INT;
SET @y = 0;
WHILE ( @y < 10 )
BEGIN
	EXEC dbo.TestProc @y;
	SET @y = @y+1;
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-23 : 14:05:12

quote:
Originally posted by sanjay5219

i was planning to have put this in loop but i have never worked on loop

not a good idea unless you've no other options

i feel like you can very easily make it into a udf as it just seems to build a hierarchy rather than anything complicated

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources