Stored Procedure (with parameters) into Temp Table without creating table fields

Question

Stored Procedure (with parameters) into Temp Table without creating table fields

Rob-350472

SSCertifiable

Points: 7197
More actions
August 19, 2011 at 6:13 am

#246604

Stored Procedure A has a bunch of parameters and it's used for an online database. I want to obtain stats from the dataset which SP A produces - i.e. perhaps SP A drives a search and returns a grid of contacts or products.
SP A cannot be edited by me, yet it will be edited from time to time by others, hence the desire to use the SP rather than the code within the SP - so if it changes so to do the stats.
Using something like:
DECLARE @Results TABLE
(ID int, Name varchar(max), Country varchar(500)
)
INSERT
INTO @Results
EXEC sp_A '','','','','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'','',0,0,NULL,NULL,'',0,0
Does work just fine, it requires no changes to the procedure and
SELECT count(id) as [Number of Records] FROM @Results
works just fine too.
However, if SP A is changed so that it returns more than those three columns then the above will break. (Not to mention the fact that some of the 'real world SPs' which I'll be working with are returning 50+ columns).
So, I'm looking to mirror the above but without explicitly defining the table first.
I've looked into this and all I can find which would achieve this is openrowset, however, it seems there's a wealth of caveats associated with this, mainly the fact that it uses a different connection so if you select into a temp table this table cannot be selected from in your main query.
Any thoughts anyone?! - I fear I'm trying to achieve the impossible with no compromise!

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

oops i misread the question...no i do not know of a way to get the results of a procedure call into a dynamic table, oither than what you already asked...openrowset calls.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rob-350472 SSCertifiable Points: 7197 More actions · Answer 2

Bah! - I read your original response and thought great! but how, I was just going to ask for further explanation.

:/ ah well... thanks anyway 🙂

Lowell SSC Guru Points: 323518 More actions · Answer 3

Sorry Rob; i thought it was a low hanging fruit question....

I assumed somethign i should not have.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 4

just one more thing...you can do it kind of, but the stored proc must return unique column names

SELECT *

INTO #temp

FROM OPENROWSET('SQLOLEDB',

'Server=DEV223;

Trusted_Connection=Yes;

Database=Master',

'Set FmtOnly OFF;

EXEC dbo.sp_Who')

the above works.

if you try to do the same with sp_who2, it fails, because that procs results has two columns named [SPID]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rob-350472 SSCertifiable Points: 7197 More actions · Answer 5

I've spoken to the person who requested this in the first place, it seems that the time it would take to build something reliable isn't worth it for its use so for now this is on hold.

However, I've picked up some useful tips in my search which will be very useful for other applications so all is not lost 😀

Thanks for the help 🙂