Skip to Content
author's profile photo Former Member
Former Member

Put the query result of "EXECUTE IMMEDIATE" command in a local table

Hi all.

Is it possible to put the output of the "EXECUTE IMMEDIATE" command in a local table so that the ouput can be accessed through other procedures.

Regards,

Andila

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Mar 12, 2014 at 09:31 AM

    Hi Andila, well you could just make your dynamic sql statement an insert statement based on your select. See example below

    create column table test_table_1

    (

    "COL1" nvarchar(10),

    "COL2" nvarchar(10)

    );

    CREATE PROCEDURE INSERT_P()

    LANGUAGE SQLSCRIPT AS

    sql_string NVARCHAR(2000) := '';

    BEGIN

    sql_string := 'insert into test_table_1 (select ''val1'', ''val2'' from dummy) ';

    EXECUTE IMMEDIATE (:sql_string);

    END;

    call insert_p();

    select * from test_table_1;

    However you may want to investigate other options instead of using dynamic SQL as this is not a recommended approach. Less optimized compared to standard sql.

    Peter

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Andila,

      You can use Local Temporary table and store your result there by using EXEC or even EXECUTE IMMEDIATE

      Which can be further captured via Table Type.

      I would say you can go for EXEC.

      Sample Code below:

      DROP PROCEDURE SAMPLE_PROC;
      CREATE PROCEDURE TEMPTABLE(OUT OUTTAB TT_EMPLOYEE)
      AS
      BEGIN
      DECLARE QUERY VARCHAR (200);
      CREATE LOCAL TEMPORARY TABLE #TEMPTABLE ( "ID" INTEGER, "NAME" VARCHAR(30));
      SQL_QUERY := 'insert into #TEMPTABLE (Select ID,NAME from EMPLOYEE )';
      EXEC (:QUERY);
      OUTTAB = SELECT * FROM #TEMPTABLE;
      DROP TABLE #TEMPTABLE;
      END;
      

      Regards,

      Krishna Tangudu

  • Posted on Mar 12, 2014 at 05:49 PM

    Hi Andila,

    You can do that as mentioned by peter in the above reply.

    But why do you want to persist the information into a Table?

    Also would want to understand if you are having a Varied "select column" list and using EXECUTE IMMEDIATE for the same?

    if not you can use "EXEC" and then use a local temporary table to hold the result which returns the value to your Table Type which can be further utilized by other procedures calling this procedure.

    Can you be more clear on the requirement on why you want to do this?

    Regards,

    Krishna Tangudu

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.