April 2, 2004 at 6:46 am
Please Help
I need to know how to use a function to modify the value of a single column for each record. With VB its easy to move from one record to the next. Whats the best way to do that with SQL 2000. I need to solve this problem urgently
My funtion is below if I call it this way I have no problem
select * from fncSplitAgentBalance(2 ,33.77)
But if I call it in a select statement as follows I get a problem
ValuationAmount is decimal(18,2) in the table
Select V.* , dbo.fncSplitAgentBalance(2,v.ValuationAmount) amt
From AsValuation v
where ValuationGUID = '155CAD65-DE64-4EF5-9CF2-941C720FAE8E '
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.fncSplitAgentBalance'.
The Function is as follows
ALTER FUNCTION dbo.fncSplitAgentBalance(@NumberOfAgents int , @Balance Decimal(18,2)
--@Balance Decimal(18,2))
RETURNS @Balances TABLE
(
AmtShare decimal(12,2),
AgentNo int
)
AS
BEGIN
--input parameters
SET @Balance = convert(Decimal(18,2),@Balance)
DECLARE @AgentNo int , @AmtShare decimal(12,2), @lastbal decimal(12,2)
IF @NumberOFAgents > 1
BEGIN
SET @AgentNo = 1
SET @AmtShare = @Balance/ @NumberOfAgents
INSERT @Balances SELECT @AmtShare , @AgentNo
WHILE @AgentNo< @NumberOfAgents - 1
BEGIN
SET @AgentNo= @AgentNo+ 1
INSERT @Balances SELECT @AmtShare , @AgentNo
END
SET @lastbal = @Balance - (@amtshare * (@NumberOfAgents-1))
INSERT @Balances SELECT @lastbal , @NumberOfAgents
END
IF @NumberOfAgents = 1
BEGIN
INSERT @Balances SELECT @Balance , @NumberOfAgents
END
RETURN
END
April 2, 2004 at 7:33 am
fncSplitAgentBalance returns table which does not match scalar requirement in select statement.
One way to tackle the problem.
cursor through AsValuation, get ValuationAmount into @variable,
call fncSplitAgentBalance (2, @variable)
get result (select or print).
April 2, 2004 at 10:52 am
Thanks I am already using that but it takes so much time. So are cursors the best way to iterate through records in SQL 2000?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply