سلام
ممنون از توجه شما
من پروسیجر را به این شکل تغییر دادم اما سر SCRLL 'گیر می ده می گه اینو نمیشناسم؟!
***من با SQL2000 کار می کنم **
CREATE PROCEDURE [SpCopyFromUntilDefShift1]
( @IdShift numeric,
@SDateOlgo [char](5),
@EDateOlgo [char](5),
@SDateRep [char](5),
@EDateRep [char](5),
@YearDate int
)
AS
declare @NoDayOlgo int, @NoDayPeriod int, @I int
declare @strTemp Char(20), @strTemp2 char(20)
set @strTemp=dbo.Persian2Civil(@YearDate ,substring( @SDateRep,1,2),substring( @SDateRep,4,2))
set @strTemp2=dbo.Persian2Civil(@YearDate ,substring( @EDateRep,1,2),substring( @EDateRep,4,2))
set @i = DATEDIFF(day,@strTemp,@strTemp2 )
set @strTemp=dbo.Persian2Civil(@YearDate ,substring( @SDateOlgo,1,2),substring( @SDateOlgo,4,2))
set @strTemp2=dbo.Persian2Civil(@YearDate ,substring( @EDateOlgo,1,2),substring( @EDateOlgo,4,2))
set @NoDayOlgo = DATEDIFF(day,@strTemp,@strTemp2) + 1
set @NoDayPeriod = Cast((@i / @NoDayOlgo)as int)
declare @DBEGINDATECur char(5)--,@DBEGINDATEOlgo char(5)
declare @DBEGINDATE char(5), @BBEGINDATE char(5), @BBEGINTIME char(5), @BENDDATE char(5), @BENDTIME char(5), @DBEGINTIME char(5), @DENDDATE char(5)
declare @DENDTIME char(5), @EBEGINTIME char(5), @EBEGINDATE char(5), @EENDDATE char(5), @EENDTIME char(5), @ENDDATE char(5), @ENDTIME char(5)
declare @HOLIDAY int
set @DBEGINDATECur =@SDateRep
--set @DBEGINDATEOlgo =@SDateOlgo
--------------
DECLARE MyCursor Cursor
SCRLL
FOR
Select DBEGINDATE, BBEGINDATE, BBEGINTIME, BENDDATE, BENDTIME, DBEGINTIME,
DENDDATE, DENDTIME, EBEGINTIME, EBEGINDATE, EENDDATE, EENDTIME, ENDDATE, ENDTIME, HOLIDAY
FROM dbo.ShiftItemDef
WHERE (TypeShiftId = @IdShift) AND (DBEGINDATE BETWEEN @SDateOlgo AND @EDateOlgo)
OPEN MyCursor
FETCH NEXT FROM MyCursor
INTO @DBEGINDATE,@BBEGINDATE, @BBEGINTIME, @BENDDATE, @BENDTIME, @DBEGINTIME, @DENDDATE,
@DENDTIME, @EBEGINTIME, @EBEGINDATE, @EENDDATE, @EENDTIME, @ENDDATE, @ENDTIME, @HOLIDAY
--------------
WHILE @DBEGINDATECur < = @EDateRep
BEGIN
set @BBEGINDATE =dbo.ReturnNewDate(@DBEGINDATE,@BBEGINDATE,@DBEGIN DATECur,@YearDate)
set @BENDDATE =dbo.ReturnNewDate(@DBEGINDATE,@BENDDATE,@DBEGINDA TECur,@YearDate)
set @DENDDATE =dbo.ReturnNewDate(@DBEGINDATE,@DENDDATE,@DBEGINDA TECur,@YearDate)
set @EBEGINDATE =dbo.ReturnNewDate(@DBEGINDATE,@EBEGINDATE,@DBEGIN DATECur,@YearDate)
set @EENDDATE =dbo.ReturnNewDate(@DBEGINDATE,@EENDDATE,@DBEGINDA TECur,@YearDate)
set @ENDDATE =dbo.ReturnNewDate(@DBEGINDATE,@ENDDATE,@DBEGINDAT ECur,@YearDate)
UPDATE ShiftItemDef
SET BBEGINDATE =@BBEGINDATE, BBEGINTIME =@BBEGINTIME, BENDDATE =@BENDDATE, BENDTIME =@BENDTIME, DBEGINTIME =@DBEGINTIME, DENDDATE =@DENDDATE,
DENDTIME =@DENDTIME, EBEGINTIME =@EBEGINTIME, EBEGINDATE =@EBEGINDATE,
EENDDATE =@EENDDATE, EENDTIME =@EENDTIME, ENDDATE =@ENDDATE, ENDTIME =@ENDTIME, HOLIDAY =@HOLIDAY
WHERE (TypeShiftId = @IdShift) AND (DBEGINDATE =@DBEGINDATECur )
/**/
set @i=@i+1
if @DBEGINDATECur= @EDateRep
begin
set @DBEGINDATECur='12/31'
break
end
else
begin
set @strTemp2=dbo.Persian2Civil(@YearDate ,substring( @DBEGINDATECur,1,2),substring( @DBEGINDATECur,4,2))
set @strTemp2 = DATEADD(day,1,@strTemp2)
set @strTemp2 = CAST(lTRIM(DATEPART(month,@strTemp2)) AS varchar(2))+ '/' +CAST(substring(@StrTemp2,5,2) AS varchar(2))+ '/' +CAST(substring(@StrTemp2,8,4) AS char(4))
set @strTemp2 =dbo.Civil2Persian(@strTemp2)
set @DBEGINDATECur = CAST(substring(@StrTemp2,6,5) AS varchar(5))
END
if @DBEGINDATE =@EDateOlgo
FETCH first FROM MyCursor INTO @DBEGINDATE, @BBEGINDATE, @BBEGINTIME, @BENDDATE, @BENDTIME, @DBEGINTIME, @DENDDATE,
@DENDTIME, @EBEGINTIME, @EBEGINDATE, @EENDDATE, @EENDTIME, @ENDDATE, @ENDTIME, @HOLIDAY
else
FETCH next FROM MyCursor INTO @DBEGINDATE, @BBEGINDATE, @BBEGINTIME, @BENDDATE, @BENDTIME, @DBEGINTIME, @DENDDATE,
@DENDTIME, @EBEGINTIME, @EBEGINDATE, @EENDDATE, @EENDTIME, @ENDDATE, @ENDTIME, @HOLIDAY
END
CLOSE MyCursor
DEALLOCATE MyCursor
GO
در ضمن وقتی از متدBREAK در IF استفاده می کنم فقط از شرط میاد بیرون در حالی که من می خوام از حلقه خارج بشه؟
بهترین روش برای خواندن n رکورد و قرار دادن در n*M رکورد در یک جدول ...
