Return temporary table from Stored procedure

  • Kristen-173977

    SSCrazy Eights

    Points: 8306

    I have a generic Sproc that I want to reuse. It returns a resulset which the Parent SProc needs to use / have access to and I want to safeguard against the column definitions, in that resulset, changing in future.

    CREATE PROCEDURE dbo.MyGenericSP
        @MyParam1  int
    , @MyParam2  varchar(10)
    AS
    SET NOCOUNT ON

      SELECT  [MyCol1] = @MyParam1 * 2
            , [MyCol2] = 'Param2=' + @MyParam2
    /** In real life:
      FROM  dbo.MyTable
      WHERE  SomeCol1 = @MyParam1
         AND SomeCol2 = @MyParam2
    **/
    GO

    Now I want to store the results from that in my Application Sproc

    CREATE PROCEDURE dbo.MyApplicationSP
        @MyParam1  int
    , @MyParam2  varchar(10)
    AS
    SET NOCOUNT ON

    CREATE TABLE #TEMP
    (
        [MyCol1]  int
      , [MyCol2]  varchar(100)
    )

      INSERT INTO #TEMP
      EXEC  dbo.MyGenericSP
          @MyParam1 = @MyParam1
        , @MyParam2 = @MyParam2
      
      SELECT  *
      FROM  #TEMP
    GO

    Test the Sproc:

    EXEC dbo.MyApplicationSP @MyParam1=123, @MyParam2='ABC'

    MyCol1      MyCol2
    ----------- ----------
    246         Param2=ABC

    Now ALTER the Sproc and change the resultset:

    ALTER PROCEDURE dbo.MyGenericSP
        @MyParam1  int
    , @MyParam2  varchar(10)
    AS
    SET NOCOUNT ON

      SELECT  [MyCol1] = @MyParam1 * 2
            , [MyCol2] = 'Param2=' + @MyParam2
    [highlight]        , [MyNewCol3] = 'Added Col3'[/highlight]
    /** In real life:
      FROM  dbo.MyTable
      WHERE  SomeCol1 = @MyParam1
         AND SomeCol2 = @MyParam2
    **/
    GO

    and the EXEC fails of course:

    EXEC dbo.MyApplicationSP @MyParam1=123, @MyParam2='ABC'

      Server: Msg 213, Level 16, State 7, Procedure MyGenericSP, Line 7
      Column name or number of supplied values does not match table definition.

    There are a number of ways of solving this that I am aware of, I would be grateful for any other suggestions

    One way is to use OPENROWSET, but I have not found a practical way to make use of that:

    SELECT  *
    INTO  #TEMP
    FROM  OPENROWSET('SQLNCLI'
       , 'Server=(local);Trusted_Connection=yes;'
       , 'SET FMTONLY OFF;
       SET NOCOUNT ON;
       EXEC MyDatabaseName.dbo.MyApplicationSP
           @MyParam1  = 123
          , @MyParam2  = ''ABC''
      ')

    I am getting:

    SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource'
    of component 'Ad Hoc Distributed Queries' because this component is turned off
    as part of the security configuration for this server.
    A system administrator can enable the use of 'Ad Hoc Distributed Queries'
    by using sp_configure.
    For more information about enabling 'Ad Hoc Distributed Queries',
    search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

    Fair enough, and I can fix that, but it is not the answer because I do not want to hardwire MyDatabaseName, and I need dynamic parameters

    The whole statement would have to be Dynamic SQL ... and of course the scope of

    SELECT  *
    INTO  #TEMP

    would then only be local to the Dynamic SQL itself. Back to Square One 🙁

    So I need a way to maintain the #TEMP definition in all the places in my code where it is used. Even that is a bit fraught, as any change needs a rollout of all effected SProcs, and that has QA testing / procedureal issues which delay rollot of a change.

    Best solution that I have seen is to have a real, dummy, table (with no rows) that is the correct structure for the resultset

    CREATE TABLE dbo.[highlight]TEMPLATE_MyGenericSP_01[/highlight]  -- "_01" in case an Sproc needs more than one #TEMP table
    (
        [MyCol1]  int
    , [MyCol2]  varchar(100)
    , [MyNewCol3] varchar(100)
    )

    and I change my Application SProc to use that definition:

    ALTER PROCEDURE dbo.MyApplicationSP
        @MyParam1  int
    , @MyParam2  varchar(10)
    AS
    SET NOCOUNT ON

    [highlight]  -- Create #TEMP from TEMPLATE_MyGenericSP_01
      SELECT  *
      INTO  #TEMP
      FROM  dbo.TEMPLATE_MyGenericSP_01
      WHERE  1=0  -- Do not accidentally select any real rows![/highlight]

      INSERT INTO #TEMP
      EXEC  dbo.MyGenericSP
          @MyParam1 = @MyParam1
        , @MyParam2 = @MyParam22

      SELECT  *
      FROM  #TEMP
    GO

    Re-test:

    EXEC dbo.MyApplicationSP @MyParam1=123, @MyParam2='ABC'

    OK ... that works 🙂

    MyCol1 MyCol2     MyNewCol3
    ------ ---------- ----------
    246    Param2=ABC Added Col3

    At that point I would probably also change MyGenericSP to dynamically create the TEMPLATE_MyGenericSP_01

    Is this change going to make all MyGenericSP inefficient? or is the change in efficiency so small that it is worth it for the code-safety?

    -- Whenever MyGenericSP script is run drop&recreate TEMPLATE_MyGenericSP_01 (in case it will have changed)
    ALTER PROCEDURE dbo.MyGenericSP
        @MyParam1  int
    , @MyParam2  varchar(10)
    AS
    SET NOCOUNT ON

      SELECT  [MyCol1] = @MyParam1 * 2
            , [MyCol2] = 'Param2=' + @MyParam2
            , [MyNewCol3] = 'Added Col3'
    [highlight]        , [MyNewCol4] = 'Added Col4'  -- Added another column, not already in the template
      INTO  #MyGenericSP_01    -- Added temporary table[/highlight]
    /** In real life:
      FROM  dbo.MyTable
      WHERE  SomeCol1 = @MyParam1
         AND SomeCol2 = @MyParam2
    **/

    [highlight]  IF OBJECT_ID('dbo.TEMPLATE_MyGenericSP_01') IS NULL
      BEGIN
       -- Create template table (if not exists)
       SELECT  *
       INTO  dbo.TEMPLATE_MyGenericSP_01
       FROM  #MyGenericSP_01
       WHERE  1=0   -- No rows!
      END

      -- Return the actual Result Set
      SELECT  *
      FROM  #MyGenericSP_01    -- Added temporary table[/highlight]

    GO
    --
    -- Recreate TEMPLATE_MyGenericSP_01 in case changed
    IF OBJECT_ID('dbo.TEMPLATE_MyGenericSP_01') IS NOT NULL
      DROP TABLE dbo.TEMPLATE_MyGenericSP_01
    -- EXEC procedure with dummy parameters to force create of XXX
    EXEC dbo.MyGenericSP @MyParam1=NULL, @MyParam2=NULL

    Re-test that the original MyApplicationSP still works, with the modified resulset from MyGenericSP

    EXEC dbo.MyApplicationSP @MyParam1=123, @MyParam2='ABC'

    MyCol1 MyCol2     MyNewCol3  [highlight]MyNewCol4[/highlight]
    ------ ---------- ---------- ----------
    246    Param2=ABC Added Col3 [highlight]Added Col4[/highlight]

    Tidy up:

    DROP PROCEDURE dbo.MyGenericSP
    GO
    DROP PROCEDURE dbo.MyApplicationSP
    GO
    DROP TABLE dbo.TEMPLATE_MyGenericSP_01
    GO

  • Jeffrey Williams 3188

    SSC Guru

    Points: 87973

    Why not just use an inline table-valued function instead of creating a #temp table everywhere?  If you absolutely must have a temp table - you could always select from the iTVF into your temp table (but I would not do that as it isn't necessary).  Any changes to the function would automatically get picked up by other procedures using the function.

    Accessing the iTVF in your procedures would depend on how you want to use it - and where you are getting the parameters.  If the parameters are fixed - you can use the function just like any other table.  If the parameters are coming from other tables to be queried - then use CROSS/OUTER APPLY.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Kristen-173977

    SSCrazy Eights

    Points: 8306

    Thanks.  The real-world Stored Procedure includes a bunch of error handling and logging, which inline table-valued function wouldn't give me, but certainly worth a thought as to whether I could live without that.

    My real-world Procedure won't even run with OPENROWSET (seems like it is too complex for OPENROWSET to parse, or somesuch, perhaps because of dependency on nested Procedures (for logging, rather than for actual computations)

  • Jeffrey Williams 3188

    SSC Guru

    Points: 87973

    Kristen-173977 - Monday, October 1, 2018 12:52 PM

    Thanks.  The real-world Stored Procedure includes a bunch of error handling and logging, which inline table-valued function wouldn't give me, but certainly worth a thought as to whether I could live without that.

    My real-world Procedure won't even run with OPENROWSET (seems like it is too complex for OPENROWSET to parse, or somesuch, perhaps because of dependency on nested Procedures (for logging, rather than for actual computations)

    I would have to see what error handling and logging are included in that procedure - generally if returning a table there really isn't any need to build in error handling and logging.

    If that is required - you could utilize a multi-statement table valued function with the caveat that it would *never* be utilized directly in a join or apply.  The only usage would be:

    SELECT * INTO #temp FROM dbo.fnTVF(parm1, parm2) AS t;

    Any changes to the function would automatically get picked up in the temp table and could then be utilized as you are now using the temp table.  This assumes that the parameters for the function would be predefined or passed into the stored procedure.  If you try to use this function in a join or apply you can expect poorer performance.

    Again - not sure why you would need any error handling or logging for a function/procedure whose only purpose is to return a table to the caller.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Jonathan AC Roberts

    SSCoach

    Points: 16489

    If you create the #temp inside the store procedure it will be deleted as soon as the stored procedure exits. you would need to create #temp outside the stored procedure:

    IF OBJECT_ID('tempdb..#TEMP','U') IS NOT NULL
        DROP TABLE #TEMP
    GO
    CREATE TABLE #TEMP
    (
      [MyCol1] int
    , [MyCol2] varchar(100)
    )
    GO
    IF OBJECT_ID('dbo.MyGenericSP','P') IS NOT NULL
        DROP PROCEDURE dbo.MyGenericSP
    GO
    CREATE PROCEDURE dbo.MyGenericSP
      @MyParam1 int
    , @MyParam2 varchar(10)
    AS
    SET NOCOUNT ON

    INSERT INTO #TEMP([MyCol1],[MyCol2]) SELECT @MyParam1,@MyParam2
    GO
    EXEC dbo.MyGenericSP 3,'three'
    EXEC dbo.MyGenericSP 4,'four'

    SELECT * FROM #TEMP

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply