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

[awitter43]

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

[awitter43]

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