2

I am inside a SQLScript procedure and would like to return the last SQL Statement from this procedure, like the last rowcount:

/********* Begin Procedure Script ************/ 
BEGIN 
    select 1 as "my_data" from dummy;
    select '::last_SQL' as "last executed sql" from dummy;
    select ::ROWCOUNT as "rowcount" from dummy;
END;
 /********* End Procedure Script ************/

The column "last executed SQL" should be populated with select 1 as "my_data" from dummy in this case. Is there any variable holding the last statement (or any easy way to retrieve the query plan)?

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Christian4145
  • 513
  • 1
  • 9
  • 31

2 Answers2

2

Maybe you can query sys.m_sql_plan_cache system view

Please check following SELECT statement

select
    statement_string, last_execution_timestamp
from SYS.M_SQL_PLAN_CACHE 
where user_name = 'KODYAZ' 
order by last_execution_timestamp desc;

I believe you can improve the query by introducing new filter criteria.

Eralper
  • 6,461
  • 2
  • 21
  • 27
0

There is no way to programmatically get the last executed SQL statement in SQLScript. This is due to the fact that not all statements will be executed in the form and order as they appear in the source code.

If you want to analyse the performance of a procedure, you can run PlanViz against the procedure call. Generally, there is no such thing as "the performance of a procedure/function" as they always occur in a specific context. If used within a SQL query, things like query-transformation can radically change the way certain results are computed.

Lars Br.
  • 9,949
  • 2
  • 15
  • 29
  • My intention was not to monitor or analyse performance. The Szenario is as follows: One department "A" would give some statements to department "B" to produce some output. Together with this output (simply some numbers in in a table) department "A" would like to be sure about which statement produced the written output. With the acepted answer it is easy to write a statement with an identifier and query the time this statement was last run and write this into the table as well. OK, you could trick out this, but all this is not about safety but something like "self-documentation". – Christian4145 Dec 05 '17 at 18:10