ALTER PROCEDURE "DBA"."Inventory_CreateOrUpdateStock"( IN @ItemCode CHAR(10), IN @Name VARCHAR(200), IN @Description VARCHAR(100), IN @Size VARCHAR(60), IN @Colour VARCHAR(60), IN @TaxCode CHAR(5), IN @Unit CHAR(10), IN @Category CHAR(20), IN @Section VARCHAR(30), IN @Product VARCHAR(30), IN @Brand VARCHAR(30), IN @SupplierId CHAR(5), IN @Hsn VARCHAR(40), IN @Plu NUMERIC(10), IN @Shelf VARCHAR(60), IN @BillINgStatus CHAR(1), IN @MinimumQuantity NUMERIC(12), IN @MaximumQuantity NUMERIC(12), IN @ReOrderLevel NUMERIC(12), IN @MinimumSalePrice NUMERIC(10) ) BEGIN MERGE INTO acc_product p (code, name, description, text3, text5, taxcode, unit, catagory, company, product, brand, supplier, text6, intcode, text4, withinstate, minimunquantity, maximumquantity, number5, nlc2) USING Inventory_CreateOrUpdateStock (@ItemCode, @Name, @Description, @Size, @Colour, @TaxCode, @Unit, @Category, @Section, @Product, @Brand, @SupplierId, @Hsn, @Plu, @Shelf, @BillINgStatus, @MinimumQuantity, @MaximumQuantity, @ReOrderLevel, @MinimumSalePrice) ON p.code= Inventory_CreateOrUpdateStock.@ItemCode WHEN MATCHED THEN UPDATE SET p.name = Inventory_CreateOrUpdateStocks.@Name, p.description = Inventory_CreateOrUpdateStock.@Description, p.text3 = Inventory_CreateOrUpdateStock.@Size, p.text5 = Inventory_CreateOrUpdateStock.@Colour, p.taxcode = Inventory_CreateOrUpdateStock.@TaxCode, p.unit = Inventory_CreateOrUpdateStock.@Unit, p.catagory = Inventory_CreateOrUpdateStock.@Category, p.company = Inventory_CreateOrUpdateStock.@Section, p.product = Inventory_CreateOrUpdateStock.@Product, p.brand = Inventory_CreateOrUpdateStock.@Brand, p.supplier = Inventory_CreateOrUpdateStock.@SupplierId, p.text6 = Inventory_CreateOrUpdateStock.@Hsn, p.intcode = CAST(Inventory_CreateOrUpdateStock.@Plu AS NUMERIC), p.text4 = Inventory_CreateOrUpdateStock.@Shelf, p.withinstate = Inventory_CreateOrUpdateStock.@BillingStatus, p.minimunquantity = Inventory_CreateOrUpdateStock.@MinimumQuantity, p.maximumquantity = Inventory_CreateOrUpdateStock.@MaximumQuantity, p.number5 = Inventory_CreateOrUpdateStock.@ReOrderLevel, p.nlc2 = Inventory_CreateOrUpdateStock.@MinimumSalePrice WHEN NOT MATCHED THEN INSERT(p.code, p.name, p.description, p.text3, p.text5, p.taxcode, p.unit, p.catagory, p.company, p.product, p.brand, p.supplier, p.text6, p.intcode, p.text4, p.withinstate, p.minimunquantity, p.maximumquantity, p.number5, p.nlc2) VALUES(Inventory_CreateOrUpdateStock.@ItemCode, Inventory_CreateOrUpdateStock.@Name, Inventory_CreateOrUpdateStock.@Description, Inventory_CreateOrUpdateStock.@Size, Inventory_CreateOrUpdateStock.@Colour, Inventory_CreateOrUpdateStock.@TaxCode, Inventory_CreateOrUpdateStock.@Unit, Inventory_CreateOrUpdateStock.@Category, Inventory_CreateOrUpdateStock.@Section, Inventory_CreateOrUpdateStock.@Product, Inventory_CreateOrUpdateStock.@Brand, Inventory_CreateOrUpdateStock.@SupplierId, Inventory_CreateOrUpdateStock.@Hsn, Inventory_CreateOrUpdateStock.@Plu, Inventory_CreateOrUpdateStock.@Shelf, Inventory_CreateOrUpdateStock.@BillINgStatus, Inventory_CreateOrUpdateStock.@MinimumQuantity, Inventory_CreateOrUpdateStock.@MaximumQuantity, Inventory_CreateOrUpdateStock.@ReOrderLevel, Inventory_CreateOrUpdateStock.@MinimumSalePrice); END *This is My stored procedure: I need to enter some data into a table acc_product if it does not exist I need to Insert or I need to update data. the procedure is getting saved but while executing it shows this error* Could not execute statement. Inventory_CreateOrUpdateStock does not return a result set SQLCODE=-872, ODBC 3 State="HY000" Line 1, column 1 |
|
Please don't ignore Volker's first question : How do you call that procedure? Like Volker, my first instinct is that the issue is related to how you call the stored procedure, not the code inside in the procedure. I can reproduce your error with the following SQL, which uses insert on existing instead of merge. create table t1 ( pk integer primary key, c1 integer not null ); create procedure p1 ( in @pk integer, in @c1 integer ) begin insert into t1 on existing update values ( @pk, @c1 ); end; select * from p1(3,3); --> Could not execute statement. --> p1 does not return a result set --> SQLCODE=-872, ODBC 3 State="HY000" However, if I call the stored procedure instead, it works great. (dba)> call p1(3,3); 1 row(s) affected (dba)> call p1(3,5); 1 row(s) affected (dba)>select * from t1; pk c1 ----- 3 5 (1 rows) |
|
Main issue here is that you are using the name of the procedure in every parameter reference, which isn't needed since the procedure does not return any records. Instead you should use SELECT with all fields: MERGE INTO acc_product p ( code, name, description, text3, text5, taxcode, unit, catagory, company, product, brand, supplier, text6, intcode, text4, withinstate, minimunquantity, maximumquantity, number5, nlc2 ) USING SELECT @ItemCode, @Name, @Description, @Size, @Colour, @TaxCode, @Unit, @Category, @Section, @Product, @Brand, @SupplierId, @Hsn, @Plu, @Shelf, @BillINgStatus, @MinimumQuantity, @MaximumQuantity, @ReOrderLevel, @MinimumSalePrice ON p.code = @ItemCode WHEN MATCHED THEN UPDATE SET p.name = @Name, p.description = @Description, ... |
How do you call that procedure?
FWIW, when you either want to insert or update entries and have a primary key in the according table, the INSERT ... ON EXISTING UPDATE statement might be easier to code than a MERGE.
Besides that, is Inventory_CreateOrUpdateStock both the name of a procedure and a table/view? If not, you reference parameters within the procedure's body simply by their name, i.e by "@ItemCode" and the like. You can't use the procedure name as prefix...
let me try first one, tnx