Substitute for OPENROWSET

[Hexonx]

I have several procs with intricate processing inside that are used in my application. I have other procs that could use the results of those procs (instead of duplicating the processing). The OPENROWSET function seems to be the way, but it requires connection info to connect back to the database. This seems rather extreme just to grab a result set that is already in the database!

Any alternatives?

 

[LogicalmanUS]

Hexonx,

I'm unsure what you mean when you state that the result set is already in the database.
When running a Sproc, the result set is alive only as long as the SProc is there, unless it is used as an output from the SProc, and in that case it is only alive as long as the process that called the SProc.

You can use one SProc to call another SProc and receive it's result record set.

I'm unsure where you are considering using OPENROWSET unless the data is in a remote DB. If it's on the current DB then OPENROWSET is not required. If it's in another database on the same server, you can use the three part reference (e.g. Master..sysobjects - where sysobjects is a table in the Master database)

Logicalman

 

[JamesLean]

Are you talking about trying to do something like this:

SELECT *
FROM (EXEC myproc1)

Where myproc1 returns some resultset?

If so, it can't be done like that. You can't use a stored proc in place of a table (or other rowset provider). You do have a couple of options though:

1) If using SQL 2000 then you could convert the SP into a table-valued function. These can be used anywhere a table can be.

2) In your calling proc, create a temp table and insert the results of the first proc into it:

CREATE TABLE #mytbl (c1 int, c2 varchar(10))

INSERT #mytbl
EXEC myproc1

SELECT * FROM #mytbl

--James

 

[Hexonx]

JamesLean has the right idea. Strange how the result set from a sproc can be pushed into a table, but we can't select from the result set as if it were a derived table:

select * from (
select * from Orders
) as MyOrders

It would be great if we could do something like:

select * from (
exec(usp_GetOrders)
)

 

[LogicalmanUS]

JamesLean,

The use of:

SELECT *
FROM (EXEC myproc1)

would serve no practical purpose.

My reference to 'You can use one SProc to call another SProc and receive it's result record set' was meant to refer to the ability to directly access the result set from a SProc as you would a standard SELECT statement when used with either a Hash/derived table or a Cursor.

This still doesn't answer the question as to what the term 'already in the database' meant, and is at the core of this question.

I would imagine the other SProcs the questioner refers to must want to utuilze the returned recordset in some shape or form prior to forming their own output.

We will have to wait and see what purpose the questioner has for the SProc outputs.

Logicalman

 

[Hexonx]

Yes, I'd like to use the result set from a sproc as if it were a select statement. And to clarify, I intend to use this inside a sproc, not in a query from an application. Some of my sprocs either have business logic that I don't want to duplicate or processing/filtering that can't occur in a single statement.

I just don't see a real difference between a result set generated by a SELECT statement in my sproc or a result set generated by a call to another sproc. Either way, I've got a result set. The source of the result set is irrelevent as long as I get what I need.

 

[maswien]

I guess the difference is that

'select * from ...' is expected following with
a existing dataset like a table or a view.

It seems the verb SELECT will do a search in the
existing dataset, starts from somewhere and ends
to somewhere, it makes sense that the dataset
should be existing and stored somewhere before
SELECT.

but the statement

'insert into <tablename> [values]' expects followed
with a group of real values or a batch of results from
an executing of a query, a batch of query or a stored
procedure. It doesn't do the search but just accepts
the results.

Hope this makes a little sense.

 

[maswien]

Query 'select * from (select * from contact) a '
but 'select * from (select * from contact)' won't work.

We can guess that SQL server create a temporary table
for 'select * from contact', because we can run following
SQL:

select * from (select * from contact) a
where a.address='20 wall'

Usually, a SP won't return the resultset of a select
statement it includes, it just output the results to
the screen but return the value that specified in the
RETURN statement.

 

[JeffModen]

Hey Hexonx,

I think what you're really asking for is &quot;how do you use the output of a stored procedure as if it were a table?&quot;

Here's an example on how to use a System Stored procudure's output as if it were a table. Watch the punctation...

SELECT a.*
FROM
OPENROWSET('MSDASQL',
'DRIVER={SQL Server};
SERVER=servernamegoeshere;
Database=databasenamegoeshere',
'EXEC SP_COLUMNS AUTHORS') AS a

--Jeff Moden