MS-SQL Sprocs - 循环，大小写，输出参数和12美分 [英] MS-SQL Sprocs - looping,case,output parameters and 12 cents
问题描述
下面的存储过程脚本是一个如何循环的例子，case /
语句和输出参数可用于MS-SQL存储的
程序来完成的事情我们可能不得不在JET世界中使用VBA
代码或自动化。一些尚未使用的人可能会感兴趣。
脚本实际上是缩进的但是我已经将它们全部对齐了
阅读更容易，（我希望）。
Sproc获取帐户ID，利率和日期，
计算每月日期当月的利息，进入
账户每日交易（如果有的话），并将每日利息总和
计算在（1 + apr ^（1/365）） -1 ...有时366.它计算每月每天的
利息（包括
利息的利息）并将其加起来，所有这些都在MS内 - SQL。这是非常快的，在几秒钟内完成了几十个账户。
但是我不发帖只是为了提供信息。我发帖是因为当我尝试在Excel中运行一个
相同的模型时，或者当我尝试运行相同的模型时，Sproc返回的值是稍微偏离了返回的值在
ASP-JScript中。例如Excel和ASP报告1月份的利息为
账户21为$ 536.35;这个MS-SQL Sproc说$ 536.23。
$ 0.12的差异不是问题，因为我可以决定使用
一种或另一种方法只要我这样做一致，这个
是可以接受的。
问题的症结在于我是3个兄弟姐妹中最年轻的并且必须
是完美的。这并不完美。由于我有三种方法和两种
给我相同的答案我假设第三种（这一种）是错误的。
但是在哪里？我检查了所有类型声明。它们似乎是一致的。
一致。我查看了扫描的日子。它们似乎是完全相同的。
我知道MS-SQL在它的类型中可以是非常特殊的（读取坚果）
强制但是我看不出它在哪里可能是一个问题，因为我在大部分时间内都进行了明确的转换。
你能看到问题吗？
更改程序sp4060148Interest
@AccountID int = 0，
@APR float = 0，
@BeginDate smalldatetime，
@GetMonthlyInterest smallmoney OUTPUT，
@GetAccountName varchar（250）OUTPUT
AS
DECLARE @APRPlusOne浮动
DECLARE @DailyInterestRate浮动
DECLARE @DaysinYear smallint
DECLARE @DaysinYearReciprocal float
DECLARE @EndDate smalldatetime
DECLARE @MonthlyInterest浮动
DECLARE @Today smalldatetime
SET @ EndDate = DATEADD（年，1，@ BeginDate）
SET @ DaysinYear = DATEDIFF（dayofyear，@ BeginDate，@ EndDate）
SET @ APRPlusOne = @ APR + 1
SET @ DaysinYearReciprocal = 1.0000000000 / @ DaysinYear
SET @ DailyInterestRate = POWER（@ APRPlusOne，@ DaysinYearRe ciprocal）-1
SET @ Today = DATEADD （dayofyear，-DAY（@BeginDate）+ 1，@ BeginDate）
SET @ MonthlyInterest = 0
WHILE MONTH（@Today）= MONTH（@ BeginDate）AND YEAR（@Today）= YEAR（@BeginDate）
BEGIN
SELECT
@ MonthlyInterest = @ MonthlyInterest +（SUM（sq。金额）+ @MonthlyInterest）* @ DailyInterestRate
FROM
（
SELECT
CASE WHEN.AbitAccountID = @AccountID THEN -t.TotalAmount ELSE
t.TotalAmount END AS Amount
FROM
[4060148Transactions] t
WHERE
（
t.DebitAccountID=@AccountID
或
t.CreditAccountID=@AccountID
）
AND
t.Date< @Today
）
AS
sq
SET @ Today = DATEADD（day，1，@ Today）
CONTINUE
END
SE T @GetMonthlyInterest = CAST（@MonthlyInterest AS smallmoney）
返回
（是的我知道我有一个冗余的参数，但它没有造成任何伤害
我现在有一个人正在欧洲旅行，他们提供这个Sproc 5
参数，我想我会等到我能改变它之后才能获得
删除额外的参数。）
-
-
Lyle
试图控制思想的人的目标始终是一样的。他们
找到一个世界的单一解释，一个思想系统和
行动将（他们相信）涵盖一切;然后他们试着将b $ b强加给所有有思想的人。
- Gilbert Highet
536.35;这个MS-SQL Sproc说
536.23。
0.12的区别不是问题因为我可以决定使用
一种或另一种方法，只要我这样做，这个
是可以接受的。
问题的关键在于我是3个兄弟姐妹中最年轻的，必须是完美的b $ b。这并不完美。由于我有三种方法和两种
给我相同的答案我假设第三种（这一种）是错误的。
但是在哪里？我检查了所有类型声明。它们似乎是一致的。
一致。我查看了扫描的日子。它们似乎是完全相同的。
我知道MS-SQL在它的类型中可以是非常特殊的（读取坚果）
强制但是我看不出它在哪里可能是一个问题，因为我在大部分时间内都进行了明确的转换。
你能看到问题吗？
更改程序sp4060148Interest
@AccountID int = 0，
@APR float = 0，
@BeginDate smalldatetime，
@GetMonthlyInterest smallmoney OUTPUT，
@GetAccountName varchar（250）OUTPUT
AS
DECLARE @APRPlusOne浮动
DECLARE @DailyInterestRate浮动
DECLARE @DaysinYear smallint
DECLARE @DaysinYearReciprocal float
DECLARE @EndDate smalldatetime
DECLARE @MonthlyInterest浮动
DECLARE @Today smalldatetime
SET @ EndDate = DATEADD（年，1，@ BeginDate）
SET @ DaysinYear = DATEDIFF（dayofyear，@ BeginDate，@ EndDate）
SET @ APRPlusOne = @ APR + 1
SET @ DaysinYearReciprocal = 1.0000000000 / @ DaysinYear
SET @ DailyInterestRate = POWER（@ APRPlusOne，@ DaysinYearRe ciprocal）-1
SET @ Today = DATEADD （dayofyear，-DAY（@BeginDate）+ 1，@ BeginDate）
SET @ MonthlyInterest = 0
WHILE MONTH（@Today）= MONTH（@ BeginDate）AND YEAR（@Today）= YEAR（@BeginDate）
BEGIN
SELECT
@ MonthlyInterest = @ MonthlyInterest +（SUM（sq。金额）+ @MonthlyInterest）* @ DailyInterestRate
FROM
（
SELECT
CASE WHEN.AbitAccountID = @AccountID THEN -t.TotalAmount ELSE
t.TotalAmount END AS Amount
FROM
[4060148Transactions] t
WHERE
（
t.DebitAccountID=@AccountID
或
t.CreditAccountID=@AccountID
）
AND
t.Date< @Today
）
AS
sq
SET @ Today = DATEADD（day，1，@ Today）
CONTINUE
END
SE T @GetMonthlyInterest = CAST（@MonthlyInterest AS smallmoney）
返回
（是的我知道我有一个冗余的参数，但它没有造成任何伤害
我现在有一个人正在欧洲旅行，他们提供这个Sproc 5
参数，我想我会等到我能改变它之后才能获得
删除额外的参数。）
-
-
Lyle
试图控制思想的人的目标始终是一样的。他们
找到一个世界的单一解释，一个思想系统和
行动将（他们相信）涵盖一切;然后他们试着将b $ b强加给所有有思想的人。
- Gilbert Highet
The stored procedure script below is an example of how looping, case
statements and output parameters can be used in MS-SQL stored
procedures to accomplish things for which we may have had to use VBA
code, or automation, in the JET world. Some who have not yet worked with
MS-SQL may be interested.
The script is indented in reality but I have aligned it all left to make
reading easier, (I hope).
The Sproc takes an Account ID, an Interest Rate, and a Date, and
calculates the monthly interest for the month of the date, taking into
account daily transactions if any, and summing the daily interest
calculated at (1+apr^(1/365))-1 ... sometimes 366. It calculates the
interest for each day of the month (including the interest on the
interest) and adds them up, all within MS-SQL. It''s quite fast, doing a
few dozen accounts in a couple of seconds.
But I do not post only to be informative. I post because the value the
Sproc returns is slightly off the value returned when I try to run an
identical model in Excel, or when I try to run an identical model in
ASP-JScript. For instance Excel and ASP report January''s interest for
Account 21 is $536.35; this MS-SQL Sproc says $536.23.
The difference of $0.12 is not a problem in that I could decide to use
one or the other method and as long as I am consistent in doing so, this
is acceptable.
The crux of the problem is that I am the youngest of 3 siblings and must
be perfect. And this is not perfect. As I have three methods and two
give me the same answer I am assuming the third (this one) is in error.
But where? I have checked all the type declarations. They seem to be
consistent. I have checked the days scanned. They seem to be identical.
I know MS-SQL can be very idiosyncratic (read nuts) in its type
coercions but I can''t see any where that this could be a problem as I
have for the most part made explicit conversions.
Can you see a problem?
ALTER PROCEDURE sp4060148Interest
@AccountID int=0,
@APR float=0,
@BeginDate smalldatetime,
@GetMonthlyInterest smallmoney OUTPUT,
@GetAccountName varchar(250) OUTPUT
AS
DECLARE @APRPlusOne float
DECLARE @DailyInterestRate float
DECLARE @DaysinYear smallint
DECLARE @DaysinYearReciprocal float
DECLARE @EndDate smalldatetime
DECLARE @MonthlyInterest float
DECLARE @Today smalldatetime
SET @EndDate=DATEADD(year,1,@BeginDate)
SET @DaysinYear=DATEDIFF(dayofyear,@BeginDate,@EndDate )
SET @APRPlusOne=@APR+1
SET @DaysinYearReciprocal=1.0000000000/@DaysinYear
SET @DailyInterestRate=POWER(@APRPlusOne,@DaysinYearRe ciprocal)-1
SET @Today=DATEADD(dayofyear,-DAY(@BeginDate)+1,@BeginDate)
SET @MonthlyInterest=0
WHILE MONTH(@Today) = MONTH(@BeginDate) AND YEAR(@Today)=YEAR(@BeginDate)
BEGIN
SELECT
@MonthlyInterest=@MonthlyInterest+(SUM(sq.Amount)+ @MonthlyInterest)*@DailyInterestRate
FROM
(
SELECT
CASE WHEN t.DebitAccountID=@AccountID THEN -t.TotalAmount ELSE
t.TotalAmount END AS Amount
FROM
[4060148Transactions] t
WHERE
(
t.DebitAccountID=@AccountID
OR
t.CreditAccountID=@AccountID
)
AND
t.Date<@Today
)
AS
sq
SET @Today=DATEADD(day,1,@Today)
CONTINUE
END
SET @GetMonthlyInterest = CAST(@MonthlyInterest AS smallmoney)
RETURN
(Yes I know I have a redundant Parameter but it is not doing any harm
and I have someone travelling in Europe right now who feeds this Sproc 5
parameters and I think I will wait until I can change that before I
remove the extra parameter.)
--
--
Lyle
"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet
536.35; this MS-SQL Sproc says
536.23.
The difference of
0.12 is not a problem in that I could decide to use
one or the other method and as long as I am consistent in doing so, this
is acceptable.
The crux of the problem is that I am the youngest of 3 siblings and must
be perfect. And this is not perfect. As I have three methods and two
give me the same answer I am assuming the third (this one) is in error.
But where? I have checked all the type declarations. They seem to be
consistent. I have checked the days scanned. They seem to be identical.
I know MS-SQL can be very idiosyncratic (read nuts) in its type
coercions but I can''t see any where that this could be a problem as I
have for the most part made explicit conversions.
Can you see a problem?
ALTER PROCEDURE sp4060148Interest
@AccountID int=0,
@APR float=0,
@BeginDate smalldatetime,
@GetMonthlyInterest smallmoney OUTPUT,
@GetAccountName varchar(250) OUTPUT
AS
DECLARE @APRPlusOne float
DECLARE @DailyInterestRate float
DECLARE @DaysinYear smallint
DECLARE @DaysinYearReciprocal float
DECLARE @EndDate smalldatetime
DECLARE @MonthlyInterest float
DECLARE @Today smalldatetime
SET @EndDate=DATEADD(year,1,@BeginDate)
SET @DaysinYear=DATEDIFF(dayofyear,@BeginDate,@EndDate )
SET @APRPlusOne=@APR+1
SET @DaysinYearReciprocal=1.0000000000/@DaysinYear
SET @DailyInterestRate=POWER(@APRPlusOne,@DaysinYearRe ciprocal)-1
SET @Today=DATEADD(dayofyear,-DAY(@BeginDate)+1,@BeginDate)
SET @MonthlyInterest=0
WHILE MONTH(@Today) = MONTH(@BeginDate) AND YEAR(@Today)=YEAR(@BeginDate)
BEGIN
SELECT
@MonthlyInterest=@MonthlyInterest+(SUM(sq.Amount)+ @MonthlyInterest)*@DailyInterestRate
FROM
(
SELECT
CASE WHEN t.DebitAccountID=@AccountID THEN -t.TotalAmount ELSE
t.TotalAmount END AS Amount
FROM
[4060148Transactions] t
WHERE
(
t.DebitAccountID=@AccountID
OR
t.CreditAccountID=@AccountID
)
AND
t.Date<@Today
)
AS
sq
SET @Today=DATEADD(day,1,@Today)
CONTINUE
END
SET @GetMonthlyInterest = CAST(@MonthlyInterest AS smallmoney)
RETURN
(Yes I know I have a redundant Parameter but it is not doing any harm
and I have someone travelling in Europe right now who feeds this Sproc 5
parameters and I think I will wait until I can change that before I
remove the extra parameter.)
--
--
Lyle
"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet
这篇关于MS-SQL Sprocs - 循环，大小写，输出参数和12美分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
