Issue with Stored Procedure

Extensibility
Enthusiast

Issue with Stored Procedure

I am working on a stored procedure for dropping all sessions for a specific user and I have written a SP to do this.  the SP creates but it fails when I run it with an id.  I can get the generated SQL out of DBQL and it runs but the SP fails.

REPLACE PROCEDURE AbortSession_sp(IN dbcidname VARCHAR(128))
  SQL SECURITY CREATOR
  BEGIN  
    DECLARE AbortStmt VARCHAR(1000);

    SET AbortStmt = 'SELECT count(syslib.ABORTSESSIONS(-1,ms.username,0,''Y'',''Y'')) cnt
FROM TABLE (syslib.MONITORSESSION(-1,''*'',0)) AS ms WHERE USERNAME=''' || dbcidname || ''';' ;
-- DBC.SYSEXECSQL(AbortStmt);

    --PREPARE AbortSQL FROM AbortStmt;
    EXECUTE IMMEDIATE AbortStmt; --USING dbcidname;*/
  end;
William Miteff
Tags (1)
6 REPLIES
Enthusiast

Re: Issue with Stored Procedure

Does anyone have suggestions on how to get this stored procedure to work?

William Miteff
Teradata Employee

Re: Issue with Stored Procedure

You can't use EXECUTE  / SysExecSQL for a SELECT. Use a dynamic cursor.

Junior Contributor

Re: Issue with Stored Procedure

If you want to abort unconditionally you don't need dynamic SQL.

ABORTSESSIONS returns a single row with the count of aborted sessions, so simply return this into a variable:

    SELECT syslib.ABORTSESSIONS(-1,'bla',0,'Y','Y') INTO CNT
FROM TABLE (syslib.MONITORSESSION(-1,'*',0)) AS ms WHERE USERNAME= :dbcidname;
Enthusiast

Re: Issue with Stored Procedure

REPLACE PROCEDURE dba_tables.AbortSession_sp(IN dbcidname VARCHAR(128))
  SQL SECURITY CREATOR
  BEGIN  
    DECLARE AbortStmt VARCHAR(1000);

    SET AbortStmt = 'SELECT count(syslib.ABORTSESSIONS(-1,''' || dbcidname || ''',0,''Y'',''Y''));';
CALL dbc.sysexecsql(AbortStmt);
  end;

i revised the procedure, and get a 5568 saying the SQL is not supported in a SP.

when i look at what was ran in DBQL i can see that "SELECT count(syslib.ABORTSESSIONS(-1,'user1',0,'Y','Y'));" was ran and failed.

William Miteff
Teradata Employee

Re: Issue with Stored Procedure

You can't execute a statement that returns data dynamically via EXECUTE or SysExecSQL. You would need to use a dynamic cursor. Or don't use dynamic SQL at all. For example

SELECT SYSLIB.ABORTSESSIONS(-1,:dbcidname,0,'Y','Y') INTO CNT;

Enthusiast

Re: Issue with Stored Procedure

That worked great, thank you so much.

William Miteff