creating a stored procedure using a function

I have a table with the the following columns
CustID      CustName      PrincipleAmount      NumberOfYears      DepositType

I have created a function that sets the principle rate
CREATE  FUNCTION  [dbo].[IntRate](@NumberofYears decimal(7,4))
      RETURNS decimal(7,4)
AS
BEGIN
      DECLARE @rate decimal(7,4)
      if @NumberofYears>=20 set @rate ='.15'
      else if @NumberofYears >= 11 set @rate ='.125'
      else if @NumberofYears>= 6 set @rate ='.10'
      else if @NumberofYears>= 4 set @rate ='.075'
      else if @NumberofYears>= 1 set @rate ='.05'
      else set @NumberofYears = '0'
      RETURN @rate
END


no i need to be able to calculate the final amount taking variable from both of these tables
PROBLEM:
I am unclear how to call the variable from the function
problem trying to solve:
Final Amount (A) – Takes three inputs P, N, and Deposit Type and returns the final amount using either simple interest formula or compound interest formula, depending upon the deposit type. For the calculation of R it uses the rate of interest function.

formulas: A=PNR (for simple interest)
                A=P(1+r)^N (for compound interest)
so i know i need an if statement based on the deposittype


I need to know how to get started or how to call the rate from the function.
Thanks
jrb47Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
>  else set @NumberofYears = '0'
?   else set @rate = 0
and you shouldn't have quotes around the rates

LIKE
SELECT  case ratetype when 'S' then numberofyears * AMT * [dbo].[IntRate](NumberofYears)
                              else amt * (1 + [ dbo].[IntRate](NumberofYears)) ** numberofyears
end

 FROM TABLE...


BUT really you should have an interest rate table which would hold the correct rate

e.g.

loanname, period, rate


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jrb47Author Commented:
I think he is wanting us to use the function to call the rate for each person and not a table. I would have set a table. How will it make a difference

0
LowfatspreadCommented:
a table is more maintainable and easier for the business to maintain and audit...

also the table can be use more eaily for historical reporting etc...
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Start This Course
jrb47Author Commented:
This is supposed to be done as a stored procedure so this is what i have come up with
CREATE Function FinalAmount(@DepositType char(6))
RETURNS real
AS
BEGIN
DECLARE @FinalAmount(real)
IF @DepositType = 'saving' set @FinalAmount = (NumberOfYears * PrincipleAmount * [dbo].[IntRate](NumberofYears)AS rate)
ELSE set @FinalAmount = PrincipleAmount * (1 + [ dbo].[IntRate](NumberofYears)AS rate) * NumberOfYears
RETURN @FinalAmount
END

AND these are the errors it generates

Msg 102, Level 15, State 1, Procedure FinalAmount, Line 5
Incorrect syntax near '('.
Msg 156, Level 15, State 1, Procedure FinalAmount, Line 6
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Procedure FinalAmount, Line 7
Incorrect syntax near the keyword 'AS'.
Msg 137, Level 15, State 2, Procedure FinalAmount, Line 8
Must declare the scalar variable "@FinalAmount".

I am not sure what i am doing wrong
I thought that i did declare the scalar variable


CREATE Function FinalAmount(@DepositType char(6))
RETURNS real
AS
BEGIN
DECLARE @FinalAmount(real)
IF @DepositType = 'saving' set @FinalAmount = (NumberOfYears * PrincipleAmount * [dbo].[IntRate](NumberofYears)AS rate)
ELSE set @FinalAmount = PrincipleAmount * (1 + [ dbo].[IntRate](NumberofYears)AS rate) * NumberOfYears
RETURN @FinalAmount
END

Open in new window

0
LowfatspreadCommented:
you've created anotehr function not a stored procedure...

is this homework?

you haven;t declare the number of years variable

and as it says get rid of the as clause
0
jrb47Author Commented:
this is schoolwork but not homework....i am trying to work through some sample questions while studying for an exam....obviously i need help before the test!

here is the revision
CREATE Function FinalAmount(@DepositType char(6))
RETURNS real
AS
BEGIN
DECLARE @FinalAmount real
DECLARE @NumberOFYears int
DECLARE @PrincipleAmount float
IF @DepositType = 'saving' set @FinalAmount = (@NumberOfYears * @PrincipleAmount * ([dbo].[IntRate](NumberofYears)))
ELSE set @FinalAmount = (@PrincipleAmount * (1 + ([ dbo].[IntRate](NumberofYears))) * @NumberOfYears)
RETURN @FinalAmount
END

here is the error
Msg 207, Level 16, State 1, Procedure FinalAmount, Line 8
Invalid column name 'NumberofYears'.

the intRate is another UDF that I am trying to call -
0
jrb47Author Commented:
actually here is the next revisions and error set!


subsequent errors
Msg 4104, Level 16, State 1, Procedure FinalAmount, Line 8
The multi-part identifier "dbo.IntRate" could not be bound.
Msg 4104, Level 16, State 1, Procedure FinalAmount, Line 9
The multi-part identifier " dbo.IntRate" could not be bound.



CREATE Function FinalAmount(@DepositType char(6))
RETURNS real
AS
BEGIN
DECLARE @FinalAmount real
DECLARE @NumberOFYears int
DECLARE @PrincipleAmount float
IF @DepositType = 'saving' set @FinalAmount = (@NumberOfYears * @PrincipleAmount * [dbo].[IntRate])
ELSE set @FinalAmount = (@PrincipleAmount * (1 + ([ dbo].[IntRate]) * @NumberOfYears))
RETURN @FinalAmount
END

Open in new window

0
jrb47Author Commented:
this is the working code
CREATE Function [dbo].[FinalAmount](@P float, @N int, @DepositType char(6))
RETURNS float
AS
BEGIN
DECLARE @FinalAmount float
IF @DepositType = 'saving' set @FinalAmount = (@P+(@N * @P * dbo.[IntRate](@N)))
IF @DepositType = 'fixed' set @FinalAmount = @p*(POWER((1+dbo.[IntRate](@N)),@N))
RETURN @FinalAmount
END

0
LowfatspreadCommented:
jrb47 has been assisted to his final solution, which differs from his original stated requirement,
plus the question was homework/coursework related.
0
jrb47Author Commented:
Whether a UDF or a PROC the starting processing is much the same - I was not asking what the formula would be -
I pay for this service so that i can get HELP in understanding how to do some of the coursework - I never make any bones about what I am working on a problem for. The agreement says the experts will not do the work FOR you - I did not ask that, in fact I posted the solution with little to no help from this person. I will close and award the points based on the answer which shows the format for using the first UDF in a call.

0
jrb47Author Commented:
see last comment
0
jrb47Author Commented:
Change the grade.
If I need to be the one to change it let me know.

I think you misunderstand my comments as harsh. It was merely an explanation as to why I am here - I am very aware that this is a volunteer service and that while I can ask I am not guaranteed an answer. However, you will note that once the question of if it was homework came up - I was not longer helped. I worked through the solution by doing more research.
I am okay with that.

The question was
I am unclear how to call the variable from the function

The answer is
CREATE Function [dbo].[FinalAmount](@P float, @N int, @DepositType char(6))

with the [dbo].FinalAmount] being how the udf should be referenced
and this dbo.[IntRate](@N)) being how it was passed -

I gave the formula that I new I would need and plainly stated that I new I would have to use an IF statement....
This done so that I could give the areas I was comfortable with

I am not saying that the person did not help - they did
0
Vee_ModCommented:
Grade change per Asker's last comment.

Vee_Mod
Experts Exchange Moderator
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.