Execution results of stored procedure

Question

Post reply

Execution results of stored procedure

SQL Guy 1

SSCoach

Points: 15979
More actions
February 26, 2009 at 10:19 am

#206345

We have a stored procedure that returns multiple result sets to the output. These result sets all have different formats.
In a code
Insert into my_table
exec my_sp
how can I specify which result set to insert ? ( I need only 2nd out of 3)
Thanks

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

two ways, but you HAVE to know which column in my_table determines the order for the ORDER BY:

I'm just assuming there is a TABLEID

SELECT * FROM (

select *, Row_number() over (ORDER BY TABLEID) AS RW From my_table ) WHERE RW=2

--OR

SELECT TOP 1 * FROM(

select top 2 * FROM my_table ORDER BY TABLEID DESC

)ORDER BY TABLEID ASC

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!

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 2

But how I can insert data into my_table in the first place ? Numbers of columns for these result sets are different.

Lowell SSC Guru Points: 323518 More actions · Answer 3

i missed that multple result part:

to insert from a EXEC(storedproc), you have to know the columns that will be returned by the stored procedure...there's no wayt around that, so if your proc returns different results sets because of dynamic queries or something, you can't do that.

as an example, here's getting the results of SP_WHO2: you HAVE to know the columns

CREATE TABLE WHORESULTS (

[WHORESULTSID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

[SPID] CHAR (5) NULL,

[INSERTDT] DATETIME NULL DEFAULT(GETDATE()),

[STATUS] VARCHAR(30) NULL,

[LOGIN] VARCHAR(30) NULL,

[HOSTNAME] VARCHAR(30) NULL,

[BLKBY] VARCHAR(30) NULL,

[DBNAME] VARCHAR(30) NULL,

[COMMAND] VARCHAR(30) NULL,

[CPUTIME] INT NULL,

[DISKIO] INT NULL,

[LASTBATCH] VARCHAR(30) NULL,

[PROGRAMNAME] VARCHAR(200) NULL,

[SPIDINT] INT NULL

)

--table exists, insert some data

INSERT INTO WHORESULTS(SPID,Status,Login,HostName,BlkBy,DBName,Command,CPUTime,DiskIO,LastBatch,ProgramName,SPIDINT)

EXEC sp_who2

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!

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 4

I DO know all the column in each result set, and I know that they are different. Unfortunately, I also suspected that this is impossible.

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 5

[font="Verdana"]You cannot do this with your existing stored procedure. It may be time to break down the stored procedure into the component parts.[/font]