sqlt-sqlstored-proceduressybasesap-ase

Insert stored procedure result set into Temp table and query temp table


I have the following stored proc which is trying to:

  1. Execute system stored procedure (sp_monitorconfig) and put the result set into a temp table.
  2. SELECT FROM this temp table and add 2 custom columns (SOURCESERVER & CollectionTime)
  3. This final result set will be ingested into Logstash via jdbc job.

I'm currently using SAP ASE 16 (sybase) and am getting an incorrect syntax error at keyword 'exec'. I'm not sure if I have to prefix the stored proc or what, but I'm currently stumped and any help is appreciated.

USE db
GO
    CREATE PROCEDURE sp_active_con_ratio.sql AS
    DECLARE @servername varchar(32) DECLARE @collecttime DATETIME DECLARE @procparam varchar(32)
select
    @servername = @@servername
select
    @collecttime = getdate()
select
    @procparam = 'number of user connections' CREATE TABLE #TempUserConnections
    (
        TempName varchar(35),
        FreeConnections int,
        ActiveConnections int,
        PercentActive char(6),
        MaxUsed int,
        Reuse_cnt int,
        Instance_Name varchar(30) NULL
    )
INSERT INTO
    #TempUserConnections (TempName, FreeConnections, ActiveConnections, PercentActive, MaxUsed, Reuse_cnt, Instance_Name)
    exec sp_monitorconfig @procparam  **ERROR HERE**
SELECT
    @servername AS 'SOURCESERVER',
    FreeConnections,
    ActiveConnections,
    PercentActive,
    MaxUsed,
    @collecttime AS 'CollectionTime'
FROM
    #TempUserConnections
    DROP TABLE #TempUserConnections
    RETURN
GO

Thanks!


Solution

  • I'd forgotten that sp_monitorconfig has an optional input parameter (@result_tbl_name) that allows the operator to designate a table into which the results should be inserted.

    From the documentation on sp_monitorconfig, Example #8 ...

    First create the table to hold the results; while the table name can vary you'll want to keep the column names/datatypes as defined:

    create table sample_table
    (Name            varchar(35),
     Config_val      int,
     System_val      int,
     Total_val       int,
     Num_free        int,
     Num_active      int,
     Pct_act         char(6),
     Max_Used        int,
     Reuse_cnt       int,
     Date            varchar(30),
     Instance_Name   varchar(35))
    

    To capture a few metrics:

    exec sp_monitorconfig "locks",            sample_table
    exec sp_monitorconfig "number of alarms", sample_table
    

    Display metrics:

    -- select * from sample_table
    
    exec sp_autoformat sample_data
    go
    
    sp_autoformat sample_table
    go
    
     Name             Config_val System_val Total_val Num_free Num_active Pct_act Max_Used Reuse_cnt Date                Instance_Name
     ---------------- ---------- ---------- --------- -------- ---------- ------- -------- --------- ------------------- -------------
     number of locks       10000        942     10000     9717        283   2.83       308         0 Aug 16 2020 12:26PM              
     number of alarms        400          0       400      386         14   3.50        14         0 Aug 16 2020 12:26PM