For .. Each result in a select

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

SQL Server 2005 Forums

Transact-SQL (2005)

For .. Each result in a select

Author

Topic

caisys
Starting Member

16 Posts

Posted - 2008-01-05 : 09:28:29

Hi,
I want to run a stored procedure for each result in a select statement, is this possible?

I have a stored procedure that shows vacations for a user between two dates, i want to show the vacations for other users in a the same team of a the user.

I can select the users first in the front end and run the sp in a for each loop but wanted to check if there is a way in sql.
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-05 : 09:35:23

You need to execute the SP inside a loop in SQL also.
Retrieve the userIDs one by one and execute the SP for each to get the results.

ie. something like

DECLARE @ID int

SELECT @ID = MIN(UserID)--first user
FROM UserTable
WHERE (users in same team as main user)


WHILE @ID IS NOT NULL
BEGIN
EXEC YourSP @UserID=@ID--get vacation info for currently retrieved user 

SELECT @ID = MIN(UserID)--iterate to next user
FROM UserTable
WHERE (users in same team as main user)
AND UserID >@ID
END

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-01-05 : 09:36:01

You could create a table-valued function that is called from your sproc that returns a small table with the results you desire, and using the results of that function, you could return the results as a table..

Your table valued function might take parameters such as team id and the vacation dates, and return the matching results.

Look at BoL for Table-Valued Function

Poor planning on your part does not constitute an emergency on my part.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-01-05 : 09:37:42

quote:
Originally posted by visakh16

You need to execute the SP inside a loop in SQL also.
Retrieve the userIDs one by one and execute the SP for each to get the results.

ie. something like
DECLARE @ID int

SELECT @ID = MIN(UserID)--first user
FROM UserTable
WHERE (users in same team as main user)

WHILE @ID IS NOT NULL
BEGIN
EXEC YourSP @UserID=@ID--get vacation info for currently retrieved user

SELECT @ID = MIN(UserID)--iterate to next user
FROM UserTable
WHERE (users in same team as main user)
AND UserID >@ID
END

You could use a cursor to do this, but since SQL is designed for set based operations, looping through records is not entirely necessary..but wrapping the existing procedure inside an outer loop which cycles the ID's is a valid, if less efficient option.

Poor planning on your part does not constitute an emergency on my part.

caisys
Starting Member

16 Posts

Posted - 2008-01-05 : 09:42:54

quote:
Originally posted by visakh16

You need to execute the SP inside a loop in SQL also.
Retrieve the userIDs one by one and execute the SP for each to get the results.

ie. something like
DECLARE @ID int

SELECT @ID = MIN(UserID)--first user
FROM UserTable
WHERE (users in same team as main user)

WHILE @ID IS NOT NULL
BEGIN
EXEC YourSP @UserID=@ID--get vacation info for currently retrieved user

SELECT @ID = MIN(UserID)--iterate to next user
FROM UserTable
WHERE (users in same team as main user)
AND UserID >@ID
END

Thanks but I can't understand how you iterated and how you are combining the results.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-01-05 : 09:54:09

THat wouldn't be exact code to use, it was a sample by the poster.

Do you need to iterate through your sproc, or do you just want to return all team members with similar vacation?

I don't believe you will need to loop anything..

You don't even necessarily need the table defined function I suggested, you can create a temp table within the sproc itself and just select from that table to return those records that match (same team and vacation dates) as those returned by your sproc.

It will be hard to help without an idea of what either your code looks like, or your table structure and sample data with desired results.

Poor planning on your part does not constitute an emergency on my part.

caisys
Starting Member

16 Posts

Posted - 2008-01-05 : 10:11:25

Here is the structure simplified:
Teams Table:
------------
TeamID int
Teamname varchar
TeamSupervisor varchar

Users:
------
UID varchar
Team int

(users can take half a day off (AM or PM) or a full day)

sp_Vacation takes (startday,endday,uid) and returns:
startday,uid,NoLeave
startday+1,uid,NoLeave
startday+2,uid,PM Leave
startday+3,uid,FullDay Leave
startday+4,uid,AM Leave
startdat+5,uid,No Leave
.
.
endday,uid,No Leave

First I will select the members of teams who have the same supervisor as the team to which a user belongs, so i will get:
uid1
uid2
uid4

I want to run the sp for each one and union the results.
The reason I am not selecting directly from the vacations table is that i check a number of related tables (national holidays, weekend days, etc.)

After I get the results for all users i will then construct an html table, one row fow for each user, 90 columns for 90 days and shade the vacation cells, this way a manager can look at the people reporting to him and check whether theier leave overlap.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-05 : 10:23:01

Where are you getting your users' vacation info from?I think you can retrieve the entire team's info and then join this with vacation info to get vacation dates of all as suggested by dataguru. Then you can dispense with the loop.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-01-05 : 10:25:14

With the limited structure, and no sample data or desired results, anything I or anyone else writes would need to be then rewritten again for your actual structure.

Seems like it would be simpler to pass the user's team ID to the sproc as well, and have your select statement return the results for all related uid's to that team.

I can't write anything as a sample with what you have provided, but this can be done with some effort on the select statement, joins and rethinking the parameters passed.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources