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

asked 21 Dec '22, 07:44

ajay's gravatar image

ajay
1113
accept rate: 0%

edited 21 Dec '22, 07:49

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...

(21 Dec '22, 07:50) Volker Barth

let me try first one, tnx

(21 Dec '22, 08:18) ajay

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)
permanent link

answered 21 Dec '22, 10:38

Reg%20Domaratzki's gravatar image

Reg Domaratzki
7.4k342113
accept rate: 38%

converted 22 Dec '22, 13:15

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,
...
permanent link

answered 03 Jan, 06:06

ArcoW's gravatar image

ArcoW
718
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×125
×123
×8

question asked: 21 Dec '22, 07:44

question was seen: 239 times

last updated: 03 Jan, 06:06