Select from a Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select from a Stored Procedure

Author

Topic

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2006-08-16 : 13:29:26

I have a stored procedure that returns a record set, can I select from the Stored Procedure?

ie. Select * from [name of stored procedure]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-16 : 13:47:01

Store the result temporarily in a table variable and select from that.

Peter Larsson
Helsingborg, Sweden

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2006-08-16 : 14:48:56

if i could change the proc i would, but i can't. thanks for the suggestion neways Peter..
ne 1 else?...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-16 : 14:55:19

You don't have to change the stored procedure to do what Peso is suggesting. Here is an example:

INSERT INTO SomeTableThatMatchesTheSproc (...)
EXEC StoredProcedureName

SELECT * FROM SomeTableThatMatchesTheSpro

Tara Kizer

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2006-08-16 : 14:58:16

But T, you have to know the structure wouldn't you? what if i want to make it dynamic?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-16 : 15:02:00

That's correct. You must know the structure.

Tara Kizer

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-16 : 15:12:20

then you do this:


if exists (select * from master..sysservers where srvname = 'loopback')
    exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback',
    @srvproduct = N'',
    @provider = N'SQLOLEDB', 
    @datasrc = @@servername
go

select * into #t from openquery(loopback, 'exec yourSproc') 
select * from #t
drop table #t
go

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-16 : 15:25:30

quote:
Originally posted by tkizer

You don't have to change the stored procedure to do what Peso is suggesting. Here is an example:

INSERT INTO SomeTableThatMatchesTheSproc (...)
EXEC StoredProcedureName

SELECT * FROM SomeTableThatMatchesTheSpro

Tara Kizer

Is this not exactly what I suggested?

Peter Larsson
Helsingborg, Sweden

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-16 : 15:29:38

Yes. But did you see her reply? I was just further reiterating your post since she thought you meant she had to modify her stored procedure.

Tara Kizer

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2006-08-18 : 15:11:57

Peso, you meant this right?
DECLARE @SSQL NVARCHAR(255)
SET @SSQL ='master..xp_readerrorlog'
INSERT TempErrorLog
EXEC sp_executesql @ssql

TempErrorLog has to be created prior, even with a table variable you have to define the table.

What I want to do is create a TempErrorLog table using the data and columns from the xp_readerrorlog proc.

Spirit; whats with the LInked Server?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-19 : 06:04:48

you need to use linked server if you want to get results of the stored procedure to a table and when
you don't know how many columns the stored procedure will return.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-21 : 01:34:09

quote:
Originally posted by Peso

Store the result temporarily in a table variable and select from that.

Peter Larsson
Helsingborg, Sweden

No it is not possible to store result of sp to table variable.
You need to use permanent or temporary table

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 03:50:44

True. I was to hasty there. Madhi is right.

EDIT: You can with SQL Server 2005 and later

Peter Larsson
Helsingborg, Sweden

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-21 : 03:51:51

can the stored procedure be changed into a UDF? if so, then it can be used in the FROM clause of any query just like a table or view.

look up table valued functions for details. I think sqlteam has a few articles on the main page about how to use them.

-ec

Saeedalhs
Starting Member

1 Post

Posted - 2008-11-13 : 02:37:44

quote:

Originally posted by spirit1

then you do this:


if exists (select * from master..sysservers where srvname = 'loopback')
    exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback',
    @srvproduct = N'',
    @provider = N'SQLOLEDB', 
    @datasrc = @@servername
go

select * into #t from openquery(loopback, 'exec yourSproc') 
select * from #t
drop table #t
go

How can we pass dynamic parameter to 'yourSproc' ? as below:
select * into #t from openquery(loopback, 'exec sp_who' + @SPID)
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-13 : 02:44:31

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70620

E 12°55'05.63"
N 56°04'39.26"

Maintex
Starting Member

4 Posts

Posted - 2008-11-26 : 05:13:13

Hi all,

I have a similar request but that link Peso provided does not allow you to have a dynamic result from a stored procedure. The way I understand it is that OpenQuery allows you to insert results into a temporary table if you do not know the structure of the results, however, it does not allow you to pass in parameters.

Is this correct? If so, is this something we are stuck with or is there another solution?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 05:29:48

quote:
Originally posted by Maintex

Hi all,

I have a similar request but that link Peso provided does not allow you to have a dynamic result from a stored procedure. The way I understand it is that OpenQuery allows you to insert results into a temporary table if you do not know the structure of the results, however, it does not allow you to pass in parameters.

Is this correct? If so, is this something we are stuck with or is there another solution?

pass parameters to table? or based on parameter? in that case use OPENQUERY with dynamic sql.

Maintex
Starting Member

4 Posts

Posted - 2008-11-26 : 06:49:00

Thank you for your quick reply visakh,

Yep, I did not explain myself very well did I lol - sorry! :(

Okay, my situation:
I have a stored procedure that I need to pass a number of parameters to which can return a different table structure depending on the parameters. I am creating another stored procedure to update some tables based on some of the information from columns from that original stored procedure. It is likely that the original stored procedure output will continue to change and so I do not want to have to keep coming back to the second stored procedure to change a table structure for each possible output of the first stored procedure.

Hope I have not lost you yet :)

So, what I need is to have OpenQuery call a stored procedure with multiple parameters and have the output (in whatever structure it comes) from the stored procedure put into a temporary table.

Ideally this would be the line but SQL Server Mgmt Studio complains about the '+' symbols:

SELECT * INTO #Temp FROM OpenQuery(LinkedServer, 'EXEC spMyStoredProc ' + @iValue1 + ', ' + @iValue2);

Please ask me if something is still not clear.

Maintex
Starting Member

4 Posts

Posted - 2008-11-26 : 07:54:18

Hmm, got a bit further now. That command works if I put the whole line into a string and execute it, unfortunately, it does not allow me to access the temporary table (probably because of the session that execute is using is different to the curent stored procedure). Now if I can just get execute to output to a temporary table...

Just so it is kept simple, I have got this to work so far:
SELECT * FROM OpenQuery(LinkedServer, 'EXEC spMyStoredProc ' + @iValue1 + ', ' + @iValue2);

but only by saving that line in a variable as a VARCHAR and 'EXECUTE'ing it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 08:09:40

quote:
Originally posted by Maintex

Hmm, got a bit further now. That command works if I put the whole line into a string and execute it, unfortunately, it does not allow me to access the temporary table (probably because of the session that execute is using is different to the curent stored procedure). Now if I can just get execute to output to a temporary table...

Just so it is kept simple, I have got this to work so far:
SELECT * FROM OpenQuery(LinkedServer, 'EXEC spMyStoredProc ' + @iValue1 + ', ' + @iValue2);

but only by saving that line in a variable as a VARCHAR and 'EXECUTE'ing it.

use sp_executesql to put results of sql string to temp table.

something like

SET @Sql=N'SELECT * FROM OpenQuery(LinkedServer, ''EXEC spMyStoredProc ''' + @iValue1 + ', ' + @iValue2+');'

SET @ParmDefinition = N'@iValue1 datatypehere,@iValue2 datatypehere';

then use
INSERT #Temp
EXEC sp_executesql @Sql, @ParmDefinition,@iValue1  = yourvalue, @iValue2=yourvalue

rememeber to declare #temp and variables before using it

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources