How to use results of an sp in another sp?

Question

How to use results of an sp in another sp?

Paul Ibison

SSCertifiable

Points: 5303
More actions
November 1, 2001 at 11:05 am

#157976

Simple question: Is it possible (in SQL 2000)to use the resultset returned from one stored procedure in another one? The only caveat to this is that we don't know beforehand the structure of the first resultset.
Paul Ibison
email: Paul.Ibison@btinternet.com

Paul Ibison
Paul.Ibison@replicationanswers.com

Viewing 9 posts - 1 through 8 (of 8 total)

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

Paul Ibison SSCertifiable Points: 5303 More actions · Answer 1

Sorry - didn't mention that we can't use a temporary table in the inner stored procedure because it is an extended stored proc. What we want is something like:

create procedure myproc as

begin

select * from exec xp_myproc

end

Paul Ibison

email: Paul.Ibison@btinternet.com

Paul Ibison
Paul.Ibison@replicationanswers.com

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

I ahven't tried this, but perhaps using the new table datatype in SQL 2000 would work?

Steve Jones

steve@dkranch.net

nigelrivett SSCertifiable Points: 5472 More actions · Answer 3

Probably not advisable but you could use openrowset to exec the SP and select from the result.

I got this working to do a select * into ... to create a temp table from an SP without knowing the SP beforehand but it took a bit of trial and error so I guess you could just use the resultset.

Cursors never.
DTS - only when needed and never to control.

Paul Ibison SSCertifiable Points: 5303 More actions · Answer 4

Thanks guys. The Table datatype would have been ideal, but unfortunately doesn't support SELECT INTO. However using the following syntax is ok:

SELECT a.* into TblNames

FROM OPENROWSET('SQLOLEDB','server';'user';'password',

'exec xp_myproc') AS a

GO

Nigel: If you can dig out the code for your second option, please could you post it up - it might be useful to compare the two.

Paul Ibison

email: Paul.Ibison@btinternet.com

Paul Ibison
Paul.Ibison@replicationanswers.com

nigelrivett SSCertifiable Points: 5472 More actions · Answer 5

It was in answer to a question in another forum but I'll see if I can find it or reproduce it.

Cursors never.
DTS - only when needed and never to control.

klaus_alandt SSC Enthusiast Points: 166 More actions · Answer 6

I don´t like passwords in my scripts concerning the openrowset-approach.

Use insert to put a resultsetset from a sp into a (temporary) table.

Example:

create table test(dirlines varchar(255) )

insert test execute ('master.dbo.xp_cmdshell "dir *.*" ')

select * from test

Is it this what you searched for?

paras_98 SSCrazy Points: 2994 More actions · Answer 7

Isn't there any alternative to OPENROWSET bcoz in OPENROWSET we have to pass username and password as 2 parameters. So when the username/password change on the server we manually need to change every part of the system. Isn't there any better way to do so?

quote:
Thanks guys. The Table datatype would have been ideal, but unfortunately doesn't support SELECT INTO. However using the following syntax is ok:
SELECT a.* into TblNames
FROM OPENROWSET('SQLOLEDB','server';'user';'password',
'exec xp_myproc') AS a
GO
Nigel: If you can dig out the code for your second option, please could you post it up - it might be useful to compare the two.
Paul Ibison
email: Paul.Ibison@btinternet.com

Paras Shah

Evision Technologies

Mumbai, India

Paras Shah
Evision Technologies
Mumbai, India

Antares686 SSC Guru Points: 125444 More actions · Answer 8

You can create a linked server to itself by creating an alias in networking client, setting that as the SQL server name and making sure data access is setup. Then you can use openquery. Two extra things to note.

1) When you do this, an individual connection that uses this link will cause an extra connection to appear and they do not close when the user connection closes. They have to reach an expiration time and are not always reused if say a use connections, uses, disconnects, reconnects and runs again. Also if you are not using a per server or per processor licensing schema or you have a extreme number of potential users you could run out of available connections

2) If you do the linked server method make sure you use an account with the minimal allowances needed to complete the task. If you use something like the sa account and have any dynamic SQL you open yourself to attack. You can use the same context the user is already connected with.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)