am aceasta procedura......de cand am introdus ORE DE PAUZA (declare si set) si WHEN.......THEN 'PAUSE'
nu vrea sa mai functioneze.....syntax e corect dar nu mai intoarce nimic...aveti idee de ce?
CREATE proc Raport_AngajatCalcTime
@gTemp_table nvarchar(500),
@QASUserName nvarchar(50),
@dataIn nvarchar(50),
@dataOut nvarchar(50)
as
declare
@QASStation nvarchar(10),
@rsCursor cursor,
@str nvarchar(4000),
@strSQL nvarchar(4000)
declare
@dataInV nvarchar(50),
@dataOutV nvarchar(50)
set @dataInV=@dataIn+' 00:00:00'
set @dataOutV=@dataOut+' 23:59:59'
---------ORE DE PAUZA
declare
@dataInP1 nvarchar(50),
@dataOutP1 nvarchar(50)
set @dataInP1=@dataIn+' 09:00:00'
set @dataOutP1=@dataOut+' 09:10:00'
-------------
declare
@dataInP2 nvarchar(50),
@dataOutP2 nvarchar(50)
set @dataInP2=@dataIn+' 11:00:00'
set @dataOutP2=@dataOut+' 11:30:00'
-------------
declare
@dataInP3 nvarchar(50),
@dataOutP3 nvarchar(50)
set @dataInP3=@dataIn+' 13:30:00'
set @dataOutP3=@dataOut+' 13:40:00'
--------------
declare
@dataInP4 nvarchar(50),
@dataOutP4 nvarchar(50)
set @dataInP4=@dataIn+' 15:30:00'
set @dataOutP4=@dataOut+' 15:40:00'
---------------
set @str='SELECT DISTINCT QASStation FROM dbo.QASLogInfo WHERE QASUserName ='''+ @QASUserName+''''
exec dbo.OpenCursor @str,@rsCursor out
while (1=1) begin
fetch next from @rsCursor into @QASStation
if @@fetch_status=-1 break
set @strSQL='insert into '+@gTemp_table+'(QASUserName,QASStation,NumeCalc,SerialNumber,
startTime,endTime,timeInWsSeconds,TimeStation,TN_Proc)
SELECT TbUser.QASUserName, Cycle.QASStation, ISNULL(dbo.N_CompName.Nume, ''Alte modele'') AS NumeCalc, Cycle.SerialNumber,
Cycle.startTime,Cycle.endTime,Cycle.timeInWsSeconds, dbo.N_QASStation.TimeStation,
CASE
WHEN dbo.N_QASStation.TimeStation=0 THEN ''SERVICE''
WHEN dbo.N_QASStation.TimeStation<>0 and Cycle.timeInWsSeconds<=dbo.N_QASStation.TimeStation THEN ''OK''
WHEN dbo.N_QASStation.TimeStation<>0 and Cycle.timeInWsSeconds>dbo.N_QASStation.TimeStation
and (Cycle.startTime BETWEEN @dataInP1 AND @dataOutP1
or Cycle.startTime BETWEEN @dataInP2 AND @dataOutP2
or Cycle.startTime BETWEEN @dataInP3 AND @dataOutP3
or Cycle.startTime BETWEEN @dataInP4 AND @dataOutP4
) THEN ''PAUSE''
ELSE ''WRONG''
END AS TN_Proc
FROM dbo.CycleTimes_'+@QASStation+' Cycle INNER JOIN
dbo.QASLogInfo TbUser ON Cycle.QASLICounter = TbUser.QASLICounter LEFT OUTER JOIN
dbo.N_QASStation ON Cycle.QASStation = dbo.N_QASStation.QASStation LEFT OUTER JOIN
dbo.N_CompName ON STUFF(Cycle.SerialNumber, 1, 13, '''') LIKE dbo.N_CompName.Seria + ''%''
WHERE TbUser.QASUserName = '''+@QASUserName+''' and Cycle.startTime>= '''+@dataInV+''' and Cycle.endTime<= '''+@dataOutV+'''
'
print @strSQL
exec sp_executesql @strSQL
end
close @rsCursor
deallocate @rsCursor
/*
set @strSQL='select * from '+@gTemp_table+' order by startTime'
exec sp_executesql @strSQL
*/
return 1
GO