The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

Hi All,

SA 12.0.1.4134

I want to create a procedure with the text:

CREATE PROCEDURE "DBA"."test"(in nID integer)
BEGIN

 insert into dba.TEST(IDENTITY, SUBITEM)
 values(nID, 3), (default, nID);
 commit;

END

When you try to save this process getting an error message:

The procedure 'test (DBA)' could not be modified in the database.
Invalid value for INSERT near 'nID' on line ???
SQLCODE: -1710
SQLSTATE: 42000

Q: What kind of error (in the help, it is not described) and why is it happening ?

asked 13 Apr '16, 02:37

Stalker's gravatar image

Stalker
23081019
accept rate: 0%

FWIW, it's not documented for v12.0.1 but for v16 and above, see here. Can't tell why it's happening, to me the INSERT statement looks good. For further tests I would use nID as a connection variable and test the INSERT statement without the surrounding stored procedure...

(13 Apr '16, 03:12) Volker Barth

Very awkward, I have tested with 2 inserts and it works fine:

CREATE PROCEDURE "DBA"."test"(in nID integer)
BEGIN

 insert into dba.TEST(IDENTITY, SUBITEM)
 values(nID, 3);

 insert into dba.TEST(IDENTITY, SUBITEM)
 values(default, nID);

 commit;

END
(13 Apr '16, 07:48) Thiago Reis

It looks like INSERT of multiple rows only accepts values.

I ran this code below on iSQL and I got the same error:

create variable @i integer;
set @i=1;
insert into dba.TEST(IDENTITY, SUBITEM) values(@i, 1), (@i, 2);

BUG?

(13 Apr '16, 07:59) Thiago Reis

Thiago is correct in his comment above: when there are multiple value tuples in the values clause then only constant values are allowed, so as a result variables are not allowed. I am unsure why there is this restriction. I have sent a note to the doc team to add a note to the VALUES clause section in the INSERT statement documentation.

[Update] After some more scanning of the code I see that host variables are also allowed in the list of values of the value row constructor when there are multiple values specified.

permanent link

answered 13 Apr '16, 08:24

Mark%20Culp's gravatar image

Mark Culp
22.3k9129262
accept rate: 40%

edited 13 Apr '16, 08:38

Syntax

INSERT INTO T ()
VALUES (), (), ();

documented for SA 12.0.1 Look in the help section "INSERT INTO statement, syntax" in the example at the end of description.

Q: So it's all the same mistake that this request does not work with the variable or not ?

(15 Apr '16, 02:52) Stalker
Replies hidden

FWIW, the doc sample with a table with columns all having defaults does work:

create table T_Test(
   col1 int default autoincrement primary key,
   col2 int default 2,
   col3 int default 3);

insert T_Test() values (), (), ();

select * from T_Test;

returns

col1,col2,col3
1,2,3
2,2,3
3,2,3

.

As Mark has pointed out, the restriction (which I would call a bug...) does only apply to multi-row insert and local (or connection-specific) variables, such as

begin
   declare nInt int = 4;
   create variable varInt int = 5;
   insert T_Test values (default, nInt, nInt); -- works
   insert T_Test values (default, varInt, varInt); -- works
   insert T_Test values (default, nInt, nInt), (default, nInt, nInt); -- fails with SQLCODE -1710
   insert T_Test values (default, varInt, varInt), (default, varInt, varInt); -- fails with SQLCODE -1710
end;
(15 Apr '16, 07:57) Volker Barth

FWIW, while the restriction seems to hold as Mark has explained, you might use an INSERT SELECT instead of an INSERT with multiple rows if you want to insert multiple rows with variables, such as

begin
   declare nInt int = 6;
   insert T_Test
   select null, nInt, nInt
   union all 
   select null, nInt, nInt;
end;
permanent link

answered 15 Apr '16, 08:05

Volker%20Barth's gravatar image

Volker Barth
29.3k287438645
accept rate: 32%

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:

×404
×79

question asked: 13 Apr '16, 02:37

question was seen: 310 times

last updated: 15 Apr '16, 08:05