@rowcount事务问题

Question

我的一笔交易有问题。我将其设置为使用@rowcount打印已被触摸的行数：

create proc Proc_Whole_Months_Work
@ProjectNum int,
@EmpId int,
@StartDate datetime,
@EndDate datetime
as
BEGIN TRANSACTION
    declare @CurrentDate datetime
    DECLARE @rowcount int
    set @CurrentDate = @StartDate

    while (@CurrentDate <= @EndDate)
    begin
        if (datepart(WEEKDAY, @CurrentDate) <=5 )--if it is a weekday
        begin
            insert into WorkHours(ProjectNum,EmpId,WorkDate,BeginTime,EndTime)values (@ProjectNum,@EmpId,@CurrentDate,'09:00:00','17:00:00')    
        end
        set @CurrentDate =DATEADD(dd, 1, @CurrentDate) 
    end
SET @rowcount = @@ROWCOUNT
--print @rowcount
if(@rowcount = 0)
    PRINT 'No rows Updated'
else
    print 'Number Of Rows Updated:' + ' ' + str(@rowcount)
COMMIT TRANSACTION

去

出于某些我似乎不理解的原因，即使有更新的行，该过程也会打印"No rows“！你知道我做错了什么吗？非常感谢！

Answer 1

@@rowcount返回受最后一条语句影响的行数，因此您必须在执行任何语句之前保存它，否则它将丢失

set @rowcount = 0
while @CurrentDate <= @EndDate
begin
    if datepart(weekday, @CurrentDate) <=5 --if it is a weekday
    begin
        insert into WorkHours(ProjectNum,EmpId,WorkDate,BeginTime,EndTime)values (@ProjectNum,@EmpId,@CurrentDate,'09:00:00','17:00:00')    
        set @rowcount = @rowcount + @@rowcount
    end
    set @CurrentDate = dateadd(day, 1, @CurrentDate) 
end

print @rowcount

我还删除了代码中的一些多余的花括号