executing the result of sql statement (EXECUTE IMMEDIATE)

[Baron]

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

[former_SQLA_member1694947]

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

[awitter]

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;
//or:
set stringtoexecute = (select 'delete from mytable where col2 = ''txt2''');
//or:
set stringtoexecute = 'delete from mytable where col2 = ''txt2''';

[fvestjens]

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 '...')

[awitter]

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))

[VolkerBarth]

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!''';

[VolkerBarth]

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"...