Problem in using result of a SP in another query

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)

Problem in using result of a SP in another query

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2009-01-07 : 03:18:46

Hi,

I have a Attendance Report which shows the Checkin and Checkout time and other details of an employee.
The totaltimeIn column(this gives the total no. of hours the employee was inside the office) is calculated thru a SP.
Now, i want to add this coulmn to my query.

The query is as shown:

Select empid
,checkin_time
,checkout_time
:
:
from attendance_details

The query goes like this. Now, i have to place the TotalINTime column
after the checkout_time.
This i will get from an SP calc_totINtime. i want sum(TotalINTime) column as my final o/p.
Can i directly use the SP in the select list?

Please suggest....its urgent.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-07 : 03:21:29

you can use sp using OPENROWSET, but its not an efficient solution.
What you should be doing is to convert sp to a scalar valued function and call it like this


Select empid
,checkin_time
,checkout_time
..,
dbo.Yourfunction(parameters...)
from attendance_details

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-01-07 : 04:43:26

Or insert the results of the proc into a temp table and then use that in your query.

CREATE TABLE #ProcResults ( .... )

INSERT INTO #ProcResults
EXEC calc_totINtime

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-07 : 09:34:49

quote:
Originally posted by GilaMonster

Or insert the results of the proc into a temp table and then use that in your query.

CREATE TABLE #ProcResults ( .... )

INSERT INTO #ProcResults
EXEC calc_totINtime

--
Gail Shaw
SQL Server MVP

in that case, temporary table should also contain required fields to join on to your main table too

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources