on 09-24-2012 11:59 AM
Hello guys:
I have two questions about EXEC,
1.)
I want to use EXEC for two different SQL statement at the same EXEC string
Ex :
create procedure INVENTORY.Henry_Test3 language SQLSCRIPT AS
BEGIN
EXEC ('INSERT INTO Henry_TestTable(Henry_TEXT, Henry_ID)
VALUES
(22,22) ; INSERT INTO Henry_TestTable(Henry_TEXT, Henry_ID)
VALUES
(23,23)');
END
When I execute this procedure, I got an error message:
Could not execute 'CALL INVENTORY.HENRY_TEST3'
SAP DBTech JDBC: [2048] (at 27): column store error: search table error: [2620] executor: plan operation failed;INVENTORY.HENRY_TEST3: line 3 col 1 (at pos 69): SQLError exception: sql syntax error: incorrect syntax near ";": line 3 col 7 (at pos 67)
2).
I want to use a procedure to create another procedure
EX:
create procedure INVENTORY.Henry_Test4 language SQLSCRIPT AS
BEGIN
EXEC ('create procedure INVENTORY.Henry_Test2 language SQLSCRIPT AS
BEGIN
EXEC (''Select * from My_LOG'');
End');
END
But I also got an error message:
Could not execute 'CALL INVENTORY.HENRY_TEST4'
SAP DBTech JDBC: [2048] (at 27): column store error: search table error: [2620] executor: plan operation failed;INVENTORY.HENRY_TEST4: line 3 col 1 (at pos 69): SQLError exception: feature not supported: create proc with ddl autocommit off mode is not supported
Could anyone help me to solve these problems?
Thanks in advance.
Hi Henry,
Please try to ammend your SQL to
CREATE PROCEDURE INVENTORY.Henry_Test4()
language SQLSCRIPT AS
BEGIN
insert into "schema"."tablename" values ('22','22');
insert into "schema"."tablename" values ('23','23');
insert into "schema"."tablename" values ('','');
END
Also you can use the wizard to create a new procedure in the content area. Also if you want to get SQL coding correct on the insert you can right click on the table you are trying to populate > Generate > Insert statement and it will automatically give you a template to use.
To call your procedure use the script like this
CALL "_SYS_BIC"."package.subpackage/Procedure name" (?)
Kind regards,
Danielle
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Henry,
I see now that you are wanting to use dynamic SQL statement.
Ive not used this before but I do know it is recommended to aviod dynamic SQL due to negative impact to security or performance. However im assumping you need it because it provides more flexibility that standard SQLscript. Perhaps you could search the forum on Dynamic SQL statements to see what is returned as that will be more specific to your EXEC issue.
Also please be aware if you arent already that SAP say that dynamic SQL results cannot bind to an SQLscript variable and that you cannot use SQLscript variables in the SQL statement(But when constructing the SQL statement string).
Kind regards,
Danielle
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.