September 4, 2012 at 10:43 am
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
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: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
EDIT: checking above reply that came while writing post...
September 4, 2012 at 10:57 am
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
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: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
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