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)
 I hate cursors!!

Author  Topic 

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2007-02-08 : 00:18:49
A while back I had to return a amount from a table in a very specific way. After much discussion I was left with no other alternative to accomplish what I wanted besides for using a cursor. Now I have a sproc (sproc_GetAmount) which requires the input variable employeeID and returns the $ amount.

exec sproc_GetAmount @EmployeeID

now the issue I have I want to use the sproc like a function and return each employee in the table like as follows (I know I can not use the sproc in the way illustrated bellow, that is the issue I am trying to resolve so I can do something like that)

Select a.*,dbo.Sproc_GetAmount(a.EmployeeiD) as amount
from tbl_Employees a

What are my options, the sproc runs a cursor, so I can not use a function, but is there any way to do this without using a second cursor and processing each employeeid seperately?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-02-08 : 00:51:57
You can put the returned result set of the stored procedure into a temp table.

INSERT INTO #GetAmount
EXEC dbo.Sproc_GetAmount...

Then you can use that table in your query. You'd probably use a join of EmployeeID between the two tables.

Tara Kizer
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-02-08 : 00:52:15
what is the result of 
exec sproc_GetAmount @EmployeeID ?


The amount return is via result set ?

KH
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-02-08 : 00:59:04
Is there a possibiliy to convert the stored procedure to a user defined function?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2007-02-08 : 01:02:59
Instead of manipulating the output of the original SP, can you change it to make EmployeeID parameter optional so that when EmployeeID is passed it will return Amount for specific employeeid, when not passed it will return amount for all employeeids?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-02-08 : 01:14:23
Please provide a link to the code of your stored procedure. Maybe it is possible to rewrite your calculation logic?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2007-02-08 : 02:53:04
The line returning the result set is "Select @Amount as money". The code in the stored procedure that ultimatly required a cursor is located at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77544

I don't know if I can covert it to a UDF just because I can not figure out a way around the cursor.

I am open to any sugestions.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-02-08 : 04:13:40
Of course you can make this as a function!
But the sample/test data you have provided is horrible, and you have made roundings in some cases and not in other cases.
However, this does the trick for you.
set dateformat mdy

-- prepare sample data
create table benefit (benefitinterestid int, benefittypeid int, startdate datetime, enddate datetime, interest decimal(5, 2))

insert	benefit
select	4, 3, '12/01/2006', '11/30/2007', .02 union all
select	3, 3, '12/01/2005', '11/30/2006', .03 union all
select	2, 3, '12/01/2004', '11/30/2005', .04 union all
select	1, 3, '12/01/2003', '11/30/2004', .05

create table interest (benefittypeid int, receiveddate datetime, amount money, interest decimal(5, 2), interestamount money)

insert	interest
select	3, '12/11/2003', 100.00, .05, 4.583 union all
select	3, '01/11/2004', 100.00, .05, 4.116 union all
select	3, '02/11/2004', 100.00, .05, 3.75 union all
select	3, '03/11/2004', 100.00, .05, 3.333 union all
select	3, '04/11/2004', 100.00, .05, 2.916 union all
select	3, '05/11/2004', 100.00, .05, 2.5 union all
select	3, '06/11/2004', 100.00, .05, 2.083 union all
select	3, '07/11/2004', 100.00, .05, 1.666 union all
select	3, '08/11/2004', 100.00, .05, 1.25 union all
select	3, '09/11/2004', 100.00, .05, 0.833 union all
select	3, '10/11/2004', 100.00, .05, 0.416 union all
select	3, '11/11/2004', 100.00, .05, 0 union all
select	3, '12/11/2004', 100.00, .04, 3.666 union all
select	3, '01/11/2005', 100.00, .04, 3.333 union all
select	3, '02/11/2005', 100.00, .04, 3 union all
select	3, '03/11/2005', 100.00, .04, 2.666 union all
select	3, '04/11/2005', 100.00, .04, 2.333 union all
select	3, '05/11/2005', 100.00, .04, 2 union all
select	3, '06/11/2005', 100.00, .04, 1.666 union all
select	3, '07/11/2005', 100.00, .04, 1.333 union all
select	3, '08/11/2005', 100.00, .04, 1 union all
select	3, '09/11/2005', 100.00, .04, 0.666 union all
select	3, '10/11/2005', 100.00, .04, 0.333 union all
select	3, '11/11/2005', 100.00, .04, 0 union all
select	3, '12/11/2005', 100.00, .03, 2.75 union all
select	3, '01/11/2006', 100.00, .03, 2.5 union all
select	3, '02/11/2006', 100.00, .03, 2.25 union all
select	3, '03/11/2006', 100.00, .03, 2 union all
select	3, '04/11/2006', 100.00, .03, 1.75 union all
select	3, '05/11/2006', 100.00, .03, 1.5 union all
select	3, '06/11/2006', 100.00, .03, 1.25 union all
select	3, '07/11/2006', 100.00, .03, 1 union all
select	3, '08/11/2006', 100.00, .03, 0.75 union all
select	3, '09/11/2006', 100.00, .03, 0.5 union all
select	3, '10/11/2006', 100.00, .03, 0.25 union all
select	3, '11/11/2006', 100.00, .03, 0 union all
select	3, '12/11/2006', 100.00, .02, 1.8333 union all
select	3, '01/11/2007', 100.00, .02, 1.666 union all
select	3, '02/11/2007', 100.00, .02, 1.5 union all
select	3, '03/11/2007', 100.00, .02, 1.333 union all
select	3, '04/11/2007', 100.00, .02, 1.166 union all
select	3, '05/11/2007', 100.00, .02, 1 union all
select	3, '06/11/2007', 100.00, .02, 0.833 union all
select	3, '07/11/2007', 100.00, .02, 0.666 union all
select	3, '08/11/2007', 100.00, .02, 0.5 union all
select	3, '09/11/2007', 100.00, .02, 0.333 union all
select	3, '10/11/2007', 100.00, .02, 0.166 union all
select	3, '11/11/2007', 100.00, .02, 0
go

CREATE FUNCTION dbo.fnGetMyInterest
(
	@EmployeeID INT
)
RETURNS MONEY
AS

BEGIN
	DECLARE	@Stage TABLE (BenefitInterestID INT PRIMARY KEY, Amount MONEY, InterestAmount MONEY, Interest DECIMAL(5, 2), ai DECIMAL(5, 2))

	INSERT		@Stage
	SELECT		b.BenefitInterestID,
			SUM(i.Amount) AS Amount,
			SUM(i.InterestAmount) AS InterestAmount,
			0,
			0
	FROM		Benefit AS b
	INNER JOIN	Interest AS i ON i.ReceivedDate >= b.StartDate AND i.ReceivedDate <= b.EndDate
--	WHERE		i.EmployeeID = @EmployeeID
	GROUP BY	b.BenefitInterestID
	ORDER BY	b.BenefitInterestID

	UPDATE	s
	SET	s.Interest = (SELECT b.Interest FROM Benefit AS b WHERE b.BenefitInterestID = s.BenefitInterestID + 1)
	FROM	@Stage AS s

	UPDATE	s1
	SET	s1.ai = s1.Interest * (SELECT SUM(s2.Amount + s2.InterestAmount) FROM @Stage AS s2 WHERE s2.BenefitInterestID <= s1.BenefitInterestID)
	FROM	@Stage AS s1

	DECLARE	@Result MONEY

	SELECT	@Result = SUM(Amount) + SUM(InterestAmount) + SUM(ai)
	FROM	@Stage

	RETURN	@Result
END
go

-- show the result
select dbo.fnGetMyInterest(1)

-- clean up
drop table benefit
drop table interest
drop function fnGetMyInterest


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2007-02-08 : 04:46:16
Peso, You are good!!, You have no clue how long I tried to figure out what you just did. I probably spent close to 20 hours before I just gave up and went with the cursor. Thanks!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-02-08 : 04:59:23
You'r welcome.
Next time, please provide sample data is displayed in ny suggestion above.
Try to keep the set as small as possible and yet have all information/combinations there.
Use the [c o d e] tag to submit your expected output based in the sample data.
The tag should be without spaces.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site