SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOALTER PROCEDURE [dbo].[GetSpecifiedEmpDuty]
(
@EmpId INT,
@DutyDate DATETIME
)
AS
DECLARE @FirstDayOfThisMonth DATETIME,
@LastDayOfThisMonth DATETIME, @pEmpId int,
@pEmpName varchar(20),
@pDutyDate datetime,
@pDutyId int,
@pDutyName varchar(20),
@pAllHalf varchar(10),
@pDailyWage decimal(18,2),
@pDailyBonus decimal(18,2),
@pDutyCount decimal(18,2),
@pRowId int,
@lActuallWage decimal(18,2)BEGIN
SET @FirstDayOfThisMonth = @DutyDate - DAY(@DutyDate) + 1
SET @LastDayOfThisMonth = DATEADD(MONTH,1,@DutyDate - DAY(@DutyDate) + 1) - 1 DECLARE dutyStatistic_cursor CURSOR FOR
SELECT EmpId,EmpName,DutyDate,DutyStatistic.DutyId,DutyTable.DutyName,
CASE WHEN DutyCount=1 THEN '全天' ELSE '半天' END AS AllHalf,DailyWage,DailyBonus,DutyCount,RowId
FROM DutyStatistic
INNER JOIN DutyTable
ON
DutyStatistic.DutyId=DutyTable.DutyId
WHERE EmpId=@EmpId
AND DutyDate>=@FirstDayOfThisMonth
AND DutyDate<=@LastDayOfThisMonth
ORDER BY DutyStatistic.DutyDate OPEN dutyStatistic_cursor
FETCH NEXT FROM dutyStatistic_cursor
INTO @pEmpId,@pEmpName,@pDutyDate,@pDutyId,@pDutyName,
@pAllHalf,@pDailyWage,@pDailyBonus,@pDutyCount,@pRowId WHILE @@FETCH_STATUS = 0
BEGIN
IF(@pDailyWage IS NULL)
BEGIN
EXEC GetEmpDailyWage @pEmpId,@pDutyId,@pDutyCount,@pDutyDate,@lActuallWage OUTPUT //问题在这里,现在这个存储过程是可以实现的.在返回结果前把数据库里的数据用UPDATE给改了,再传给前台;
//我想实现的是:把这个返回的值赋给游标,即暂不修改记录值,由用户来点击是否保存,来决定修改数据库值.
//不用下面的UPDATE语句了. //例如: --dutyStatistic_cursor.DailyWage = @lActuallWage
//但是不行,有错.哪位大哥帮忙看看,这个应该怎么写.谢谢.语言有些乱,凑合的吧. UPDATE DutyStatistic
SET DailyWage = @lActuallWage,DailyBonus = 0
WHERE RowId = @pRowId
END FETCH NEXT FROM dutyStatistic_cursor
INTO @pEmpId,@pEmpName,@pDutyDate,@pDutyId,@pDutyName,
@pAllHalf,@pDailyWage,@pDailyBonus,@pDutyCount,@pRowId END CLOSE dutyStatistic_cursor
DEALLOCATE dutyStatistic_cursor SELECT EmpId,EmpName,DutyDate,DutyStatistic.DutyId,DutyTable.DutyName,
CASE WHEN DutyCount=1 THEN '全天' ELSE '半天' END AS AllHalf,DailyWage,DailyBonus,DutyCount,RowId
FROM DutyStatistic
INNER JOIN DutyTable
ON
DutyStatistic.DutyId=DutyTable.DutyId
WHERE EmpId=@EmpId
AND DutyDate>=@FirstDayOfThisMonth
AND DutyDate<=@LastDayOfThisMonth
ORDER BY DutyStatistic.DutyDateENDGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GO
SET ANSI_NULLS ON
GOALTER PROCEDURE [dbo].[GetSpecifiedEmpDuty]
(
@EmpId INT,
@DutyDate DATETIME
)
AS
DECLARE @FirstDayOfThisMonth DATETIME,
@LastDayOfThisMonth DATETIME, @pEmpId int,
@pEmpName varchar(20),
@pDutyDate datetime,
@pDutyId int,
@pDutyName varchar(20),
@pAllHalf varchar(10),
@pDailyWage decimal(18,2),
@pDailyBonus decimal(18,2),
@pDutyCount decimal(18,2),
@pRowId int,
@lActuallWage decimal(18,2)BEGIN
SET @FirstDayOfThisMonth = @DutyDate - DAY(@DutyDate) + 1
SET @LastDayOfThisMonth = DATEADD(MONTH,1,@DutyDate - DAY(@DutyDate) + 1) - 1 DECLARE dutyStatistic_cursor CURSOR FOR
SELECT EmpId,EmpName,DutyDate,DutyStatistic.DutyId,DutyTable.DutyName,
CASE WHEN DutyCount=1 THEN '全天' ELSE '半天' END AS AllHalf,DailyWage,DailyBonus,DutyCount,RowId
FROM DutyStatistic
INNER JOIN DutyTable
ON
DutyStatistic.DutyId=DutyTable.DutyId
WHERE EmpId=@EmpId
AND DutyDate>=@FirstDayOfThisMonth
AND DutyDate<=@LastDayOfThisMonth
ORDER BY DutyStatistic.DutyDate OPEN dutyStatistic_cursor
FETCH NEXT FROM dutyStatistic_cursor
INTO @pEmpId,@pEmpName,@pDutyDate,@pDutyId,@pDutyName,
@pAllHalf,@pDailyWage,@pDailyBonus,@pDutyCount,@pRowId WHILE @@FETCH_STATUS = 0
BEGIN
IF(@pDailyWage IS NULL)
BEGIN
EXEC GetEmpDailyWage @pEmpId,@pDutyId,@pDutyCount,@pDutyDate,@lActuallWage OUTPUT //问题在这里,现在这个存储过程是可以实现的.在返回结果前把数据库里的数据用UPDATE给改了,再传给前台;
//我想实现的是:把这个返回的值赋给游标,即暂不修改记录值,由用户来点击是否保存,来决定修改数据库值.
//不用下面的UPDATE语句了. //例如: --dutyStatistic_cursor.DailyWage = @lActuallWage
//但是不行,有错.哪位大哥帮忙看看,这个应该怎么写.谢谢.语言有些乱,凑合的吧. UPDATE DutyStatistic
SET DailyWage = @lActuallWage,DailyBonus = 0
WHERE RowId = @pRowId
END FETCH NEXT FROM dutyStatistic_cursor
INTO @pEmpId,@pEmpName,@pDutyDate,@pDutyId,@pDutyName,
@pAllHalf,@pDailyWage,@pDailyBonus,@pDutyCount,@pRowId END CLOSE dutyStatistic_cursor
DEALLOCATE dutyStatistic_cursor SELECT EmpId,EmpName,DutyDate,DutyStatistic.DutyId,DutyTable.DutyName,
CASE WHEN DutyCount=1 THEN '全天' ELSE '半天' END AS AllHalf,DailyWage,DailyBonus,DutyCount,RowId
FROM DutyStatistic
INNER JOIN DutyTable
ON
DutyStatistic.DutyId=DutyTable.DutyId
WHERE EmpId=@EmpId
AND DutyDate>=@FirstDayOfThisMonth
AND DutyDate<=@LastDayOfThisMonth
ORDER BY DutyStatistic.DutyDateENDGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货