on 2022 Dec 21 7:44 AM
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
Request clarification before answering.
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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, ...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
52 | |
10 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.