Hi All,
my problem is that I'm trying to execute a script for creating a Stored Procedure in Delphi. Everything was fine till the moment I put a parameter like :Name(Field), everything passes fine(without exceptions) , but the procedure is not created. Please help me, give some opinions, I'm desperate. Thank u and here is the source of the procedure
procedure TForm1.FormCreate(Sender: TObject);
begin
try
with TIBQuery.Create(nil) do
try
Database := GetDB;
SQl.Text := ' CREATE PROCEDURE PROC_DONEOBSRV ' +
' RETURNS (' +
' STAFFNAME VARCHAR(100),' +
' STAFFUIN VARCHAR(10),' +
' MDDNO INTEGER,' +
' DATEPRINT DATE,' +
' PATINAME VARCHAR(100),' +
' PATIEGN VARCHAR(10),' +
' OBSRVCOUNT_ALL INTEGER,' +
' OBSRVCOUNT_DONE INTEGER,' +
' TOTAL DOUBLE PRECISION)' +
' AS ' +
' begin ' +#13#10+
' FOR ' +
' select ls.FDOER_NAME, ' +
' ls.FDOER_UIN, ' +
' j.FMDDNO, ' +
' j.FDATEPRINT, ' +
' j.FPATIFULLNAME, ' +
' j.FPATIEGN, ' +
' count(lo.FID) as cnt_all ' +
' from TLABOJOURNAL j ' +
' join TLABO_DOERSSTAFF ls on (j.FSTAFFID = ls.FID) ' +
' join TLABOOBSRV lo on (lo.FJOURNALID = j.FID) ' +
' where j.FSTATUS = 0 and ' +
' ls.FACTIVE = 0 ' +
' GROUP BY ls.FDOER_NAME, ' +
' j.FMDDNO, ' +
' j.FPATIFULLNAME, ' +
' j.FPATIEGN, ' +
' ls.FDOER_UIN, ' +
' j.FDATEPRINT ' +
' into StaffName, StaffUIN, MDDNo,DatePrint,PatiName, PatiEGN, ObsrvCount_All ' +//this is one row
' DO ' +
' begin ' +
' select count(lo.FID), ' +
' sum(lo.FCODEPRICE) ' +
' from TLABOJOURNAL j ' +
' join TLABOOBSRV lo on lo.FJOURNALID = j.FID ' +
' where j.FMDDNO = :MDDNo and ' +//the parameter ??
' j.FSTATUS = 0 and ' +
' lo.FSTATUS = 0 ' +
' into ObsrvCount_done, Total; ' +
' suspend; ' +
' end ' +
' end ' ;
ExecSQL;
finally
Free;
end;
except ShowMessage('Unable to create Procedure'); Exit end;
ShowMessage('Procedure Created');
end;
