Execute two INSERTS in one stored procedure

[former_SQLA_member1694895]

Hi

I have the following stored procedure

ALTER PROCEDURE dba.jg_x_new_pn @oldpn varchar(25)
AS

BEGIN

DECLARE @newpn varchar(25)
SET @newpn = string('78',dba.getnextprimary('primary_key'))
INSERT INTO dba.table1
SELECT opco_code,@newpn,proddesc,prodcateg,prodsell,discmat,sequence,obsolete,prodcost,replace(memo,'9010','9003'),qtytype,qtydecimal,qtyfactor,allowsplit,stocked,selldecimals,costdecimals,pack,altref1,altref2,altref3,altref4,privatememo,lastupdatedtimestamp,lastupdated,lastupdatedby,created,createdby,allowlinediscount,allowoveralldiscount,allowsettlementdiscount,saleprice,nextprice,salefrom,saleto,nextpricestart,vatcode,unitdescription,purchaseanalysis,salesanalysis,warranty,warrantydefault,defaultwarehouse,unitweight,unitweightdecimals,unitweightdesc,unitvolume,unitvolumedecimals,unitvolumedesc,prodtype,webcatalog,webprodref,webproddesc,webprodextdesc,weballowsplit,hazardsubstance,recipientrecordrequired,datenextavailable,qtyavailable,barcode,parentprodref,variationdesc,specification,sys_a_objects_key,purchasecurrency,purchasecost,purchasecostdecimals,cataloguecode,catalogueref,manufacturer,manufacturerref,templateprodref,isatemplate,binlocation,costcentre,department,webvalidation,deliverychargetype,excludefromoverallpromotion,defaultsupersededby,supersededqtyratio,supersededqtymultiply
FROM dba.table1
WHERE prodref = @oldpn
SELECT @newpn
INSERT INTO dba.table2 (id, prodref, notes)
VALUES (getNextPrimary('primary_key_notes.id'), (SELECT @newpn), 'I MADE THIS')
END
COMMIT;

The first insert command runs fine, but the second INSERT command doesn't do anything. I was expecting it to INSERT a new line into table2 using the new part number (@newpn).

What am I doing wrong?

[VolkerBarth]

Hm you are mixing SQL dialects, methinks. Most statements and the lack of the ";" statement delimiter look like T-SQL whereas the "SET @newpn = " seems Watcom-SQL syntax. I don't know whether this does cause undesired effects, at least it makes it difficult to tell when a new statement starts...

What is the "select @newpn" as a single statement ought to do? It will return a result set to the caller. If you do not need that, comment it out.

FWIW, in the second INSERT, you can simply use the local variable as part of the VALUES clause, you don't require a SELECT around it, such as

...), @newpn, 'I MADE THIS')