sql count using stored procedure withing stored procedure

I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing

for each user name in a temporary table (already done)

total = result from execute totaling stored procedure

Can you help with this

Thanks

0
ajaxrobert
4/29/2008 2:37:07 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

10 Replies
788 Views

Similar Articles

[PageSpeed] 27

It would be easier if you can change the stored procedure into a function. Once you do that, the total can be calculated easily with something like this

Select Sum(dbo.CalculateHours(UserName))

Fro  UserTable 

 


Save our world, its all we have! A must watch video Pale Blue Dot

Please use the search feature of the forum before asking a question.
0
XPSCodes
4/29/2008 3:41:13 PM

I am so confused with this. I am currently using temporary tables (that is tables with an s because there are multiple of them) the tables are created with rows from the select statements with each row having a name and hours. I really need one table with a row for each user and their total hours. Can these tables be combined of better yet one table created with the data?

0
ajaxrobert
4/29/2008 4:20:49 PM

The function call

Select Sum(dbo.CalculateHours(UserName))

The UserName is where I need the many names that are in a table right now so it process all of them and generates many results

0
ajaxrobert
4/29/2008 4:36:34 PM

 

can i use

Select Sum(dbo.CalculateHours(select UserID from #table Name))

 

0
ajaxrobert
4/29/2008 4:39:18 PM

I a not pursuing the multiple table solution but the one from above

EXEC @return_value = [dbo].[procName]

@ID = 193

and the correct value is returned but how do I grab it?

 

0
ajaxrobert
4/29/2008 5:48:05 PM

Its done like this: 

 Select Sum(dbo.CalculateHours(UserID)) From #table


Save our world, its all we have! A must watch video Pale Blue Dot

Please use the search feature of the forum before asking a question.
0
XPSCodes
4/29/2008 5:53:04 PM

 

The Stored Procedure uses a temp table.

Can a temporary table be used in a function?

0
ajaxrobert
4/29/2008 6:37:00 PM

 Yes, I believe we can use the function. Cant remember ever using it, but I think we can.


Save our world, its all we have! A must watch video Pale Blue Dot

Please use the search feature of the forum before asking a question.
0
XPSCodes
4/29/2008 7:00:01 PM

I get the message

Cannot access temporary tables from within a function.

when I verify the function with

SELECT sum(myTime) as Hours

into #tblHours

There must be another solution for this

0
ajaxrobert
4/29/2008 7:54:10 PM

you can Use Table Variables instead of temporary tables. They can be useful performancewise also


Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
0
ramireddyindia
4/30/2008 4:21:06 AM
Reply: