SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Select from a Stored Procedure
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

DBASlut
Yak Posting Veteran

71 Posts

Posted - 08/16/2006 :  13:29:26  Show Profile  Reply with Quote
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

Sweden
30241 Posts

Posted - 08/16/2006 :  13:47:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 08/16/2006 :  14:48:56  Show Profile  Reply with Quote
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

USA
36932 Posts

Posted - 08/16/2006 :  14:55:19  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 08/16/2006 :  14:58:16  Show Profile  Reply with Quote
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

USA
36932 Posts

Posted - 08/16/2006 :  15:02:00  Show Profile  Visit tkizer's Homepage  Reply with Quote
That's correct. You must know the structure.

Tara Kizer
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/16/2006 :  15:12:20  Show Profile  Visit spirit1's Homepage  Reply with Quote
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

Sweden
30241 Posts

Posted - 08/16/2006 :  15:25:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
36932 Posts

Posted - 08/16/2006 :  15:29:38  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 08/18/2006 :  15:11:57  Show Profile  Reply with Quote
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

Slovenia
11751 Posts

Posted - 08/19/2006 :  06:04:48  Show Profile  Visit spirit1's Homepage  Reply with Quote
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

India
22755 Posts

Posted - 08/21/2006 :  01:34:09  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Sweden
30241 Posts

Posted - 08/21/2006 :  03:50:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
True. I was to hasty there. Madhi is right.

EDIT: You can with SQL Server 2005 and later

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 11/26/2008 05:20:18
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 08/21/2006 :  03:51:51  Show Profile  Reply with Quote
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 Posts

Posted - 11/13/2008 :  02:37:44  Show Profile  Visit Saeedalhs's Homepage  Reply with Quote
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

Sweden
30241 Posts

Posted - 11/13/2008 :  02:44:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

United Kingdom
4 Posts

Posted - 11/26/2008 :  05:13:13  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 11/26/2008 :  05:29:48  Show Profile  Reply with Quote
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

United Kingdom
4 Posts

Posted - 11/26/2008 :  06:49:00  Show Profile  Reply with Quote
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

United Kingdom
4 Posts

Posted - 11/26/2008 :  07:54:18  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 11/26/2008 :  08:09:40  Show Profile  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000