Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

I am trying to execute a statement, the statement is based on the content of one table, so that I should 'select' some table and then pass the result of the sql statement to EXECUTE STATEMENT.

I understand that EXECUTE IMMEDIATE expects a string and not a result set, so its clear why 2 and 3 below dont work, but I am surprised why 5 below works!!

create or replace table mytable(col1 int, col2 varchar(10));
insert into mytable values (1, 'txt1'), (2, 'txt2');
execute immediate ('delete from mytable where col2 = ''txt2''');--1 OK
execute immediate (select 'delete from mytable where col2 = ''txt2''' );--2 NOK
execute immediate ((select 'delete from mytable where col2 = ''txt2''' ));--3 NOK
execute immediate (string (select 'delete from mytable where col2 = ''txt2''' ));--4 NOK
execute immediate ('' || (select 'delete from mytable where col2 = ''txt2''' ));--5 OK

asked 13 Dec '22, 06:02

Baron's gravatar image

accept rate: 48%

Hm, check the syntax (*), the statement string does not expect brackets around it, it's simply put after EXECUTE IMMEDIATE, such as

EXECUTE IMMEDIATE 'DELETE MyTable where col2 = ''txt2''';

Furthermore, for statements with a result set, there's the WITH RESULT SET ON clause.

(*): Or to be correct, the "EXECUTE (string-expression)" is T-SQL syntax, whereas the EXECUTE IMMEDIATE is Watcom-SQL, so you seem to use a - probably undefined - mix of SQL dialects?

That being said, what exactly does "pass the result set ... to execute immediate" mean? If you want to use DML statements based on queries, that is usually be done via joins in the DML statement (DELETE...FROM...), not requiring dynamic SQL at all, and if you want to get the rows affected by a DML statement, you can use DML-derived-tables aka "SELECT over a DML statement"...

permanent link

answered 13 Dec '22, 07:17

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

edited 13 Dec '22, 07:23

execute immediate ('' || (select 'delete from mytable where col2 = ''txt2''' ));

Of cause this is ok, because the result of the select is the string 'delete from mytable where col2 = 'txt2' and that is a correct string to execute immediate.

That string is equal to say:

declare stringtoexecute long nvarchar;
select 'delete from mytable where col2 = ''txt2''' into stringtoexecute;
set stringtoexecute = (select 'delete from mytable where col2 = ''txt2''');
set stringtoexecute = 'delete from mytable where col2 = ''txt2''';
permanent link

answered 14 Dec '22, 04:41

ArcoW's gravatar image

accept rate: 0%

edited 14 Dec '22, 04:42

If you would do

execute immediate(stringtoexecute)

that would work As far as I know execute immediate executes a string expression and can't execute statements like for example (select '...')

(14 Dec '22, 07:06) Frank Vestjens

The reason is that '' || (select 'hello') actually is a string. Or better: including the ( and ). This is because || takes the arguments and try to cast them to what is possible. In this case, starting with '' || means it 'needs' to be a string, so it makes it as a string. This will do the same:

execute immediate cast((select 'delete from mytable where col2 = ''txt2''') as nchar(100))
(14 Dec '22, 10:15) ArcoW
Replies hidden

Yes, but we still wait for the OP to tell what he is really about to do - if it was just to issue a DELETE statement via EXECUTE IMMEDIATE, the syntax for that is obvious (see my answer) and does not require to mess around with brackets and string concatenation and sub queries and the like. :)

And even for statements with a result set, one does not need brackets, here's how to use a SELECT with a string literal:

execute immediate with result set on 'select ''Hello World!''';
(14 Dec '22, 10:33) Volker Barth

Hello this is Gulshan Negi Well, I searched about it on the internet and I found that The reason why 2, 3, and 4 are not working is that they are trying to pass a result set as an argument to the EXECUTE IMMEDIATE statement. As you mentioned correctly, EXECUTE IMMEDIATE expects a string as an argument. In contrast, the reason why 5 works is that it converts the result set to a string by concatenating an empty string to it. This creates a string that contains the SQL statement, which can be executed by the EXECUTE IMMEDIATE statement. Note that this approach may not work in all cases, especially if the result set is large, in which case concatenating an empty string to it may cause performance issues. In such cases, you may want to consider using a cursor to iterate over the result set and execute the SQL statement for each row. Thanks

permanent link

answered 28 Apr '23, 08:54

gulshan212's gravatar image

accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 13 Dec '22, 06:02

question was seen: 803 times

last updated: 28 Apr '23, 08:54