I have the following script that I need to use parameters with.  I need the line USING 92,7 to be replaced with paramters for the values.  I'm still learning this newer version of SQL and don't know how to accomplish this.  The script works but only with constants.

SCRIPT
BEGIN
DECLARE MyCursor SENSITIVE CURSOR WITH RETURN FOR MyStmt;
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE Temp2';
EXCEPTION
END;
EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE Temp2 AS
     Select T1.sOrder, T1.ProductID, T1.OrderID, P2.ProductCode, T1.ProductionID, P.TagNumber, T1.LocationID, L.Name, True Selection
     FROM (Select 0 sOrder, T.ProductID, T.OrderID, T.ProductionID, T.LocationID, Sum(T.Quantity) Sum_Quantity
           From Trans T
           where orderid= ? and productid= ?
           Group By productid,orderid,productionid,locationid) T1
     Left Outer Join Production P On P.ID = T1.ProductionID
     Left Outer Join Products P2 On P2.ID = T1.ProductID
     Left Outer Join Locations L On L.ID = T1.LocationID
     Where T1.Sum_Quantity < 0
     WITH DATA' USING 92,7 ;
EXECUTE IMMEDIATE 'CREATE INDEX MyIndex ON Temp2 (sorder,tagnumber)';
PREPARE MyStmt FROM 'SELECT * FROM Temp2 ORDER BY sorder, tagnumber';
OPEN MyCursor;
END

gbh100


I can tell you how to get parameters in there and how to use them. No idea if the script will work though

To add parameters to a script you need to do something like

SCRIPT (IN Order_ID INTEGER, IN Product_ID INTEGER)

then in Delphi you set the parameters for the script just as you would for a query eg

edbscript.ParamByName('Order_ID').AsInteger := 92;


To use them in the script you use a ? as placeholder so your code would become

USING ?,?

They are used in the order they are listed in the parameter statement so here the ? would become 92,7


Roy Lambert

Thanks,  this works fine . . .

SCRIPT (IN iOrderID INTEGER, IN iProductID INTEGER)
BEGIN
DECLARE MyCursor SENSITIVE CURSOR WITH RETURN FOR MyStmt;
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE Temp2';
EXCEPTION
END;
EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE Temp2 AS
     Select T1.sOrder, T1.ProductID, T1.OrderID, P2.ProductCode, T1.ProductionID, P.TagNumber, T1.LocationID, L.Name, True Selection
     FROM (Select 0 sOrder, T.ProductID, T.OrderID, T.ProductionID, T.LocationID, Sum(T.Quantity) Sum_Quantity
           From Trans T
           where orderid= ? and productid= ?
           Group By productid,orderid,productionid,locationid) T1
     Left Outer Join Production P On P.ID = T1.ProductionID
     Left Outer Join Products P2 On P2.ID = T1.ProductID
     Left Outer Join Locations L On L.ID = T1.LocationID
     Where T1.Sum_Quantity < 0
     WITH DATA' USING iOrderID, iProductID ;
EXECUTE IMMEDIATE 'CREATE INDEX MyIndex ON Temp2 (sorder,tagnumber)';
PREPARE MyStmt FROM 'SELECT * FROM Temp2 ORDER BY sorder, tagnumber';
OPEN MyCursor;
END