Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Stored Procedures

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;

Comments

Sign In or Register to comment.