T-SQL

Re: script gresit....dar unde?

Alex — Tue, 13 Feb 2007 10:35:31 GMT

Razvan, multumesc de raspuns

nu am creat eu procedurile respective incerc doar sa le modific dupa niste cerinte (sincer e prima data cand vad niste proceduri create in felul asta.....pe respectiva baza de date toate sunt create in felul ala)....multumesc pt linkuri nu am apucat inca sa ma uit pe ele dar o s-o fac cu siguranta

alex

Re: script gresit....dar unde?

rsocol — Wed, 07 Feb 2007 16:16:29 GMT

N-am citit atent codul tău, dar mi se pare cam mult dynamic SQL şi peste tot e vulneriabil la SQL Injection. Citeşte:
http://www.sommarskog.se/dynamic_sql.html
http://www.nextgenss.com/papers/advanced_sql_injection.pdf

Răzvan

Re: script gresit....dar unde?

Alex — Wed, 07 Feb 2007 15:08:36 GMT

am rezolvat pana la urma.......am modificat partea cu 'WHEN...TEHN PAUSE' cu

WHEN dbo.N_QASStation.TimeStation<>0 and Cycle.timeInWsSeconds>dbo.N_QASStation.TimeStation
and ((Cycle.startTime>= '''+@dataInP1+''' and Cycle.startTime<= '''+@dataOutP1+''')
  or (Cycle.startTime>= '''+@dataInP2+''' and Cycle.startTime<= '''+@dataOutP2+''')
  or (Cycle.startTime>= '''+@dataInP3+''' and Cycle.startTime<= '''+@dataOutP3+''')
  or (Cycle.startTime>= '''+@dataInP4+''' and Cycle.startTime<= '''+@dataOutP4+''')
) THEN ''PAUSE''

sunt sigur ca exista si o varianta mai simpla dar nu am stiut cum altfel (ma refer la toata partea cu dataINP1,P2 etc...la cum sunt declarate ele etc)

Re: script gresit....dar unde?

Alex — Wed, 07 Feb 2007 14:24:49 GMT

e vorba cred de "Syntax error converting datetime from character string."....acolo al BETWEEN..... cum as putea rezolva problema...aveti idee?

script gresit....dar unde?

Alex — Wed, 07 Feb 2007 13:18:11 GMT

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