日期参数的存储过程调用出错
刚刚开始搞，目前日期参数的存储过程调用出错，比如
CREATE PROCEDURE CountUserServDaysInMonth
@UserId int = null,
@ServiceId int = null,
@RORDate datetime = null
AS
BEGIN
PRINT 'RORDate is:'+CONVERT(datetime, @RORDate,101);
DECLARE @ServiceStartTimeRec datetime,@ServiceEndTimeRec datetime,@recNum int=0,@sumDays int=0;
SELECT @recNum = count(*) from UserServiceLog where UserId=@UserId and ServiceId=@ServiceId and @RORDate>= ServiceStartTime and @RORDate<= ServiceEndTime;
PRINT 'record num is:'+CONVERT(varchar, @recNum);
DECLARE cur_old_style CURSOR FOR select ServiceStartTime,ServiceEndTime from UserServiceLog where UserId=@UserId and ServiceId=@ServiceId and @RORDate>= ServiceStartTime and @RORDate<= ServiceEndTime;
OPEN cur_old_style
FETCH cur_old_style INTO @ServiceStartTimeRec,@ServiceEndTimeRec;
WHILE (@@fetch_status=0)
BEGIN
--根据类型来判断增加还是减少，但目前缺乏类型标识
DECLARE @i AS INT = 0;
select @i=DATEDIFF(day,@ServiceStartTimeRec,@ServiceEndTimeRec);
select @sumDays=@sumDays+@i;
FETCH cur_old_style INTO @ServiceStartTimeRec,@ServiceEndTimeRec;
END
CLOSE cur_old_style;
DEALLOCATE cur_old_style;
PRINT 'Days in this month is:'+CONVERT(varchar, @sumDays);
END
EXEC CountUserServDaysInMonth @UserId=56038925,@ServiceId=1,@RORDate='2010-06-30 23:59:59.0';
