Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 3 of 3 total |
how to get parameter information into SCRIPT |
Thu, Dec 21 2017 6:01 PM | Permanent Link |
Greg Hallam Microcalm Solutions Inc | 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 |
Fri, Dec 22 2017 2:30 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Fri, Dec 22 2017 6:51 AM | Permanent Link |
Greg Hallam Microcalm Solutions Inc | 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 |
This web page was last updated on Saturday, May 4, 2024 at 09:18 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |