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
Go to Top of Page

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?...
Go to Top of Page

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
Go to Top of Page

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?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-16 : 15:02:00
That's correct. You must know the structure.

Tara Kizer
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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?
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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"
Go to Top of Page

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?
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page
    Next Page

- Advertisement -