How to convert from VB to sql function? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How to convert from VB to sql function?

Author

Topic

sweetgorgor
Starting Member

2 Posts

Posted - 2008-08-05 : 16:48:45

Hi, could someone tell me how to convert the following function from access to SQL? I am not sure how the syntax for loop in sql server 2005.

Here is the access(Vb) code:

Function NewAmAmount(upb As Double, maxupb As Double, intr As Double, pymtamt As Double, term As Integer) As Double

Dim i As Integer
i = 0

Do While upb < maxupb And term > i
upb = upb * (1 + intr) - pymtamt
i = i + 1
Loop

NewAmAmount = upb
End Function

Thanks!
Dave

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-06 : 06:45:17

Assuming the doubles are floats...


CREATE FUNCTION [dbo].[NewAmAmount] (
	@upb FLOAT
	, @maxupb FLOAT
	, @intr FLOAT
	, @pymtamt FLOAT
	, @term INT
	)
	RETURNS FLOAT
AS BEGIN

	DECLARE @i INT                        SET @i = 0

	WHILE ( @upb < @maxupb ) AND ( @term > @i ) BEGIN

		SET @upb = @upb * (1 + @intr) - @pymtamt
		SET @i = @i + 1
	END

	RETURN @upb
END

-------------
Charlie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-06 : 07:35:40

Seems like an ordinary interest summing function.
Why are you using a loop instead of using a formula?

E 12°55'05.25"
N 56°04'39.16"

sweetgorgor
Starting Member

2 Posts

Posted - 2008-08-06 : 11:41:23

Hi peso,
because I want it to stop when the condition upb < maxupb and when term > i. therefore i need the loop.

Thanks Charles i will give it a try.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-06 : 11:46:58

No, you don't need the loop. Just put the number of periods in the formula.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-06 : 11:59:23

http://math.about.com/library/weekly/aa042002a.htm

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources