Post reply

Using OPENROWSET to execute stored procedure in temp table

  • September 4, 2012 at 10:43 am

    #267256

    In need to execute a stored procedure and run some calculations against the results. One way I've found to do this is by using OPENROWSET. Here's one of the articles I found:

    http://sqlserverplanet.com/sql/insert-stored-procedure-results-into-table

    For my own learning I'm just trying to understand this. Does OPENROWSET create another instance to execute the sp against? I'm trying to understand what exactly is going on here. Any insight is appreciated!

  • September 4, 2012 at 10:52 am

    #1533403

    If my stored procedure invokes a SELECT, I'd normally just put it inside an INSERT clause, like so:

    CREATE PROCEDURE myProc

    AS BEGIN

    SELECT [name]

    FROM sys.databases

    WHERE [name] NOT IN ('master','msdb','tempdb','model')

    AND state_desc = 'ONLINE'

    ORDER BY [name] ASC

    END

    CREATE TABLE outputTable ( database_name VARCHAR(100) )

    INSERT INTO outputTable

    EXEC myProc

    Is this what you meant, or have I missed the point?

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • September 4, 2012 at 10:53 am

    #1533406

    EDIT: checking above reply that came while writing post...

  • September 4, 2012 at 10:57 am

    #1533408

    Thanks Derek. Would that create a permanent table called outputTable? Just to clarify, I already have a stored procedure created. I just need to put the results of the sp in a temp table or something so I can write a query against it.

  • September 4, 2012 at 11:07 am

    #1533412

    In the example given, outputTable is a permanent table.

    You could use a table variable or a local/global temporary table if that would suit you, providing you know the outputs of your SP.

    Using the SP above...

    DECLARE @t1 TABLE ( database_name VARCHAR(100) )

    INSERT INTO @t1

    EXEC myProc

    The @t1 table lasts only as long as the execution, so you could add a query immediately below, i.e.

    SELECT database_name FROM @t1 WHERE database_name LIKE '%customers%'

    Replace @ with # for a local table, with ## for a global table, and if using # or ## the definition varies, not DECLARE @t1 TABLE but CREATE TABLE #t1. Hope that makes sense.

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • September 4, 2012 at 3:19 pm

    #1533541

    DataAnalyst011 (9/4/2012)

    Does OPENROWSET create another instance to execute the sp against?

    Yes, it does. It creates a new session outside the database engine as if it were any other data client that happens to connect back to itself. The technique is a nice? little hack, but it will not scale. It also requires that you enable ad hoc distributed queries which is disabled by default as it can pose security risks.

    I would also recommend using the technique derek.colley presented. There are limitations to it as you can only have one INSERT...EXEC in your call stack so be aware that you cannot roll the technique out everywhere without some careful planning. If this is a one-off need though it should do fine, you just need to nkow the output schema ahead of time which you do not need to know if you use OPENROWSET.

    If you have the option to change the stored procedure evanluate whether you can convert it into a multi-statement table-valued function (mTVF). mTVFs have their limitations too (e.g. cannot call procs in them) but if you're not subject to them and you'll only ever use them to pipe the results into a temporary table then they'll act just like the OPENROWSET method in that you will not need to know the output schema ahead of time (i.e. you can use SELECT...INTO with them). Just don't bother joining to an mTVF as if they were regular tables. The only table-valued functions worthwhile for that type of usage are the inline versions (iTVF).

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

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

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