CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
RobMartin
RobMartin

Reputation: 59

Select results from stored procedure into a table

I have a stored procedure, usp_region and it has a select statement with 50 columns as the result set. This procedure is called by multiple other stored procedures in our application.

Most of the stored procedure pass a parameter to this procedure and display the result set that it returns. I have one stored procedure, usp_calculatedDisplay, that gets the columns from this stored procedure and inserts the values into a temp table and does some more calculations on the columns.

Here's a part of the code in usp_calculatedDisplay.

Begin Procedure

/* some sql statements */

Declare #tmptable
(
    -- all the 50 columns that are returned from the usp_region procedure
)

Insert Into #tmptable
    exec usp_region @regionId = @id

Select t.*, /* a few calculated columns here */
From #tmptable t

End of procedure

Every time I add a column to the usp_region procedure, I'll also have to make sure I have to add it to this procedure. Otherwise it breaks. It has become difficult to maintain it since it is highly possible for someone to miss adding a column to the usp_calculatedDisplay procedure when the column is added to the usp_region.

In order to overcome this problem, I decided to do this:

Select *
Into #tmptable
From OPENROWSET('SQLNCLI', 
                'Server=localhost;Trusted_Connection=yes;', 
                'EXEC [dbo].[usp_region]')

The problem is 'Ad Hoc Distributed Queries' component is turned off. So I can't use this approach to overcome this issue. I was wondering if there are any other ways of overcoming this problem. I would really appreciate any help. Thank you!

Upvotes: 3

Views: 7784

Answers (1)

Ronen Ariely
Ronen Ariely

Reputation: 2434

Every time I add a column to the usp_region procedure

SQL Server is a structured database and it does not meant to solve such cases that you need to change your structure every day.

If you add/remove columns so often then you probably did not choose the right type of database, and you better re-design your system.

It has become difficult to maintain it since it is highly possible for someone to miss adding a column to the usp_calculatedDisplay procedure when the column is added to the usp_region.

There are two simple solutions for this (1) using DDL Triggers - very bad idea but simple to implement and working. (2) Using my trick to select from stored procedure

Option 1: using DDL trigger

You can automate the entire procedure and ALTER the stored procedure usp_calculatedDisplay every time that the stored procedure usp_region is changed

https://learn.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers

The basic approach is

CREATE OR ALTER TRIGGER NotGoodSolutionTrig ON DATABASE FOR ALTER_PROCEDURE AS BEGIN
    DECLARE @var_xml XML = EVENTDATA();
    IF(
        @var_xml.value('(EVENT_INSTANCE/DatabaseName)[1]', 'sysname') = 'tempdb'
        and 
        @var_xml.value('(EVENT_INSTANCE/SchemaName)[1]', 'sysname') = 'dbo'
        and 
        @var_xml.value('(EVENT_INSTANCE/ObjectName)[1]', 'sysname') = 'usp_region'
        )
        BEGIN
            -- Here you can parse the text of the stored procedure 
            -- and execute ALTER on the first SP
            -- To make it simpler, you can design the procedure usp_region so the columns names will be in specific row or between to comment which will help us to find it
        
            -- The code of the Stored Procedure which you need to parse is in the value of:
            -- @var_xml.value('(EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)'))
            -- For example we can print it
            DECLARE @SP_Code NVARCHAR(MAX)
            SET @SP_Code = CONVERT(NVARCHAR(MAX), @var_xml.value('(EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)'))
            PRINT @SP_Code
            
            -- In your case, you need to execute ALTER on the usp_calculatedDisplay procedure using the text from usp_region
        END
END

Option 2: trick to select from stored procedure using sys.dm_exec_describe_first_result_set

This is simple and direct way to get what you need.

CREATE OR ALTER PROCEDURE usp_calculatedDisplay AS 

    -- Option: using simple table, so it will exists outsie the scope of the dynamic query
    DROP TABLE IF EXISTS MyTable;
    DECLARE @sqlCommand NVARCHAR(MAX)
    select @sqlCommand = 'CREATE TABLE MyTable(' + STRING_AGG ([name] + ' ' +  system_type_name, ',') + ');'
    from sys.dm_exec_describe_first_result_set (N'EXEC usp_region', null,0)
    PRINT @sqlCommand
    EXECUTE sp_executesql @sqlCommand

    INSERT MyTable EXECUTE usp_region;

    SELECT * FROM MyTable;

GO

Note!!! Both solutions are not recommended in production. My advice is to avoid such needs by redesign your system. If you need to re-write 20 SP so do it and don't be lazy! Your goal should be what best for the database usage.

Upvotes: 2

Related Questions