cancel
Showing results for 
Search instead for 
Did you mean: 

two questions about EXEC

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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

Former Member
0 Kudos

Dear  Danielle:

Thanks for your help.

I know how to create a normal procedure, but beacuse of some reason,


I have to use EXEC to create a procedure or combine two different SQL statement at the same EXEC string

But thank for your help, I'm very appreciate.

Henry

Former Member
0 Kudos

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

Answers (0)