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

Question

I have the following stored proc which is trying to:

Execute system stored procedure (sp_monitorconfig) and put the result set into a temp table.
SELECT FROM this temp table and add 2 custom columns (SOURCESERVER & CollectionTime)
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!

You don't tell us what that error is. Also sp_ is a reserved prefix by Microsoft and should not be used for User Procedures. — Larnu
@Larnu Thanks for the comment. Sorry, error is just "Incorrect syntax near the keyword exec." Also, I altered the db name and sp name because they both included company info :) — basement
@basement this code is running without error on my environment !. — Vahid Farahmandian
insert / exec is not supported in Sybase ASE; while you can use a proxy table to insert proc output into a table this is a bit convoluted; your best bet would be to extract the desired queries from the source of sp_monitorconfig and build your own/custom sql to do what you want — markp-fuso

markp-fuso markp-fuso · Accepted Answer · 2020-08-16T17:28:15

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
2> 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

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

2 Answers