Stored Procedures - Programmers Heaven

Howdy, Stranger!

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

Categories

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

  • sziszi81sziszi81 Posts: 80Member
    You did not COMMIT the transaction.
  • progrprogr Posts: 4Member
    : You did not COMMIT the transaction.
    : I think that;s notthe problem, because even if I put the Commit statement in the source, it doesn't want to Run because of the ':', infront of my variable. It says: "Token unknown" for the ":".
  • progrprogr Posts: 4Member
    Guys I found the decision. It is about the parameters. The paramCheck property has to be False, because otherwise the parameters of the SQL statement are considered as parameters of the Query, not as parameters of the procedure.
Sign In or Register to comment.