ALTER PROCEDURE [dbo].[Proc_Test]
(
@Time datetime,
@SUMValue decimal output
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ReturnValue decimal
DECLARE MyCursor CURSOR FOR SELECT YUEZONGJ FROM v_Syn_BIZ_MSP_LISTMS Where MEASDATE < @Time
DECLARE @T_T decimal
OPEN MyCursor
FETCH NEXT FROM MyCursor into @T_T
WHILE @@FETCH_STATUS = 0
BEGIN
set @ReturnValue =+round(N'12'*isnull(@T_T,0),0)
FETCH NEXT FROM MyCursor;
ENDCLOSE MyCursor
DEALLOCATE MyCursor
END以上为存储过程:请问这个能实现 set @ReturnValue =+round(N'12'*isnull(@T_T,0),0)
的累加吗?我最后要返回@ReturnValue 的数值。
(
@Time datetime,
@SUMValue decimal output
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ReturnValue decimal
DECLARE MyCursor CURSOR FOR SELECT YUEZONGJ FROM v_Syn_BIZ_MSP_LISTMS Where MEASDATE < @Time
DECLARE @T_T decimal
OPEN MyCursor
FETCH NEXT FROM MyCursor into @T_T
WHILE @@FETCH_STATUS = 0
BEGIN
set @ReturnValue =+round(N'12'*isnull(@T_T,0),0)
FETCH NEXT FROM MyCursor;
ENDCLOSE MyCursor
DEALLOCATE MyCursor
END以上为存储过程:请问这个能实现 set @ReturnValue =+round(N'12'*isnull(@T_T,0),0)
的累加吗?我最后要返回@ReturnValue 的数值。
循环内
set @ReturnValue =@ReturnValue+round(N'12'*isnull(@T_T,0),0)
(
@Time datetime,
@SUMValue decimal output
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ReturnValue decimal
select @ReturnValue = sum(round(12*isnull(YUEZONGJ,0))) from v_Syn_BIZ_MSP_LISTMS Where MEASDATE < @Time
END
ALTER PROCEDURE [dbo].[Proc_Test](@Time datetime,@SUMValue decimal output)
AS
BEGIN
SET NOCOUNT ON
select @SUMValue = sum(round(12*isnull(YUEZONGJ,0)))
from v_Syn_BIZ_MSP_LISTMS Where MEASDATE < @Time
END
(
@Time datetime,
@SUMValue decimal(10,2) output
)
ASBEGIN
SET NOCOUNT ON;
DECLARE @ReturnValue decimal(10,2)
set @ReturnValue=0
DECLARE MyCursor CURSOR FOR SELECT YUEZONGJ FROM v_Syn_BIZ_MSP_LISTMS Where MEASDATE < @Time
DECLARE @T_T decimal(10,2)
OPEN MyCursor
FETCH NEXT FROM MyCursor into @T_T
WHILE @@FETCH_STATUS = 0
BEGIN
set @ReturnValue =@ReturnValue+round(N'12'*isnull(@T_T,0),0)FETCH NEXT FROM MyCursor into @T_T
ENDCLOSE MyCursor
DEALLOCATE MyCursor
END
from v_Syn_BIZ_MSP_LISTMS Where MEASDATE < @Time
数据库是怎么来执行的了?是取出一行*12。然后把所有的都加吗?数据库怎么做到这点的啊。
----------------------------------------------
是的.2.数据库怎么做到这点的啊。
----------------------------------------------
sum()是聚合函数,即逐行累加.
代码如下:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Proc_Test]
(
@Time datetime,
@SUMValue decimal(10,2) output
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ReturnValue decimal(10,2)
set @ReturnValue=0.00
DECLARE MyCursor CURSOR FOR SELECT YUEZONGJ FROM v_Syn_BIZ_MSP_LISTMS Where MEASDATE < @Time
DECLARE @T_T decimal
OPEN MyCursor
FETCH NEXT FROM MyCursor into @T_T
WHILE @@FETCH_STATUS = 0
BEGIN
set @ReturnValue =@ReturnValue+round(N'12'*isnull(@T_T,0),0)
FETCH NEXT FROM MyCursor;
ENDCLOSE MyCursor
DEALLOCATE MyCursor
END
CLOSE MyCursor
DEALLOCATE MyCursor
set @SUMValue = @ReturnValue /*将累加的值赋予输出型参数*/
END
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Proc_Test]
(
@Time datetime,
@SUMValue decimal(10,2) output
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ReturnValue1 decimal(10,2)
set @ReturnValue1=0.00
DECLARE MyCursor CURSOR FOR SELECT YUEZONGJ FROM v_Syn_BIZ_MSP_LISTMS Where MEASDATE < @Time
DECLARE @T_T decimal
OPEN MyCursor
FETCH NEXT FROM MyCursor into @T_T
WHILE @@FETCH_STATUS = 0
BEGIN
set @ReturnValue1 =@ReturnValue1+round(N'12'*isnull(@T_T,0),0)
FETCH NEXT FROM MyCursor;
ENDCLOSE MyCursor
DEALLOCATE MyCursor
--select @SUMValue = sum(round(12*isnull(YUEZONGJ,0),0)) from v_Syn_BIZ_MSP_LISTMS Where MEASDATE < @Time--select @SUMValue = round(12*sum(isnull(YUEZONGJ,0)),0) from v_Syn_BIZ_MSP_LISTMS Where MEASDATE < @Time
set @SUMValue = @ReturnValue1 END
(
@Time datetime,
@SUMValue decimal(10,2) output
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ReturnValue decimal(10,2)
set @ReturnValue=0.00
DECLARE MyCursor CURSOR FOR SELECT YUEZONGJ FROM v_Syn_BIZ_MSP_LISTMS Where MEASDATE < @Time
DECLARE @T_T decimal
OPEN MyCursor
FETCH NEXT FROM MyCursor into @T_T
WHILE @@FETCH_STATUS = 0
BEGIN
set @ReturnValue =@ReturnValue+round(N'12'*isnull(@T_T,0),0)
FETCH NEXT FROM MyCursor into @T_T /*加上into @T_T*/
ENDCLOSE MyCursor
DEALLOCATE MyCursor
set @SUMValue = @ReturnValue /*将累加的值赋予输出型参数*/
END
我现在这里有三个思路:
1。set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Proc_Test]
(
@Time datetime,
@SUMValue decimal(10,2) output
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ReturnValue1 decimal(10,2)
set @ReturnValue1=0.00
DECLARE MyCursor CURSOR FOR SELECT YUEZONGJ FROM v_Syn_BIZ_MSP_LISTMS Where MEASDATE < @Time
DECLARE @T_T decimal
OPEN MyCursor
FETCH NEXT FROM MyCursor into @T_T
WHILE @@FETCH_STATUS = 0
BEGIN
set @ReturnValue1 =@ReturnValue1+round(N'12'*isnull(@T_T,0),0)
FETCH NEXT FROM MyCursor into @T_T;
ENDCLOSE MyCursor
DEALLOCATE MyCursor
2。--select @SUMValue = sum(round(12*isnull(YUEZONGJ,0),0)) from v_Syn_BIZ_MSP_LISTMS Where MEASDATE < @Time3。--select @SUMValue = round(12*sum(isnull(YUEZONGJ,0)),0) from v_Syn_BIZ_MSP_LISTMS Where MEASDATE < @Time
set @SUMValue = @ReturnValue1 END最后:调试结果为:15319740,15319759,15319757为什么会这样了。 应该是。1和2思路的结果一样才对啊。
非常谢谢。
而
select @SUMValue = round(12*sum(isnull(YUEZONGJ,0)),0) from v_Syn_BIZ_MSP_LISTMS Where MEASDATE < @Time
这种累加方式完全没有截断YUEZONGJ列的值,保持了原有的精度,这个是最准确的.
如果希望游标以上二种方式计算的值最接近,有二种办法:
方法1:
将@T_T声明为float类型,即declare @T_T float
方法2:将YUEZONGJ转换为DECIMAL(10,2)类型
select @SUMValue = round(12*sum(isnull(CAST(YUEZONGJ AS DECIMAL(10,2)),0)),0) from v_Syn_BIZ_MSP_LISTMS Where MEASDATE < @Time