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?

asked 08 Nov '16, 04:48

Jongee's gravatar image

Jongee
17271117
accept rate: 0%

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')
(08 Nov '16, 05:29) Volker Barth
Be the first one to answer this question!
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:

×106
×99
×29

question asked: 08 Nov '16, 04:48

question was seen: 232 times

last updated: 08 Nov '16, 05:31