cancel
Showing results for 
Search instead for 
Did you mean: 

Merge and Insert from stored procedure

0 Kudos
1,078
    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

Accepted Solutions (0)

Answers (2)

Answers (2)

regdomaratzki
Product and Topic Expert
Product and Topic Expert

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)
awitter
Participant
0 Kudos

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