Can I Do a Select *FROM StoredProcedure ??

Question

Can I Do a Select *FROM StoredProcedure ??

MrBungle-581032

SSC Rookie

Points: 34
More actions
March 7, 2008 at 9:19 am

#187562

I've been looking on the internet for this, but i don't if its not posible or i'm not making a good search, if anyone knows, thnks

Viewing 12 posts - 1 through 12 (of 12 total)

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

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

You can only select the results of a stored procedure by dumping the results into a table. You have to make sure that your table has the same number of returned columns as the stored procedure. In addition to a table, you can store the results into a table variable, and temp table.

e.g.

create table mytable(

col1 int,

...

)

insert into mytable

exec mystoredprocedure

select *

from mytable

My blog: http://jahaines.blogspot.com

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 2

I'm not so sure that's true, Adam. I believe you can use OPENQUERY for this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Adam Haines SSC-Insane Points: 23217 More actions · Answer 3

I'm not so sure that's true, Adam. I believe you can use OPENQUERY for this.

This is what I get for speaking in absolute :hehe:. Well on a positive note I learned something new. I have never used this function before, but will research it to find out more.

Edit: Remove only

You can select the results of a stored procedure by dumping the results into a table. You have to make sure that your table has the same number of returned columns as the stored procedure. In addition to a table, you can store the results into a table variable, and temp table.

My blog: http://jahaines.blogspot.com

Adam Haines SSC-Insane Points: 23217 More actions · Answer 4

After briefly looking at the function. I would still use the proposed solution for performance benifts, but will acknowledge that openquery can accomplish the task.

My blog: http://jahaines.blogspot.com

MrBungle-581032 SSC Rookie Points: 34 More actions · Answer 5

Ok, with openquery i found two problems, according to the msdn Library, open query only returns the first row of the query and it doesn't work for stored procedures with parameters... so, i can't use openquery. I'm already using temp tables, but i'm triying to avoid them because i think that its not good for performance and sometimes i have to create temp tables with 100 columns, and in the end i only use five, so i think is a waste. Thanks, see you on the road

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 6

What? One row? You'll need to show me that URL... take a look at Books Online, instead... example they give uses SELECT TOP 1000 *...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MrBungle-581032 SSC Rookie Points: 34 More actions · Answer 7

Alright here is, http://technet.microsoft.com/es-es/library/ms188427.aspx, its in spanish. i hope you can understand... so my stored procedure has parameters, OPENQUERY can still work for me?

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 8

Sorry... I can't read Spanish...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Adam Haines SSC-Insane Points: 23217 More actions · Answer 9

The link he posted is the same as BOL. This is the remark he is concerned with

OPENQUERY does not accept variables for its arguments.
In SQL Server 2000 and later versions, OPENQUERY cannot be used to execute extended stored procedures on a linked server. However, an extended stored procedure can be executed on a linked server by using a four-part name. For example:

link http://msdn2.microsoft.com/en-us/library/ms188427.aspx

My blog: http://jahaines.blogspot.com

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

Jeff Moden (3/7/2008)
Sorry... I can't read Spanish...

All right smarty....He's pointing at the BOL entry for OPENQUERY...

http://technet.microsoft.com/en-us/library/ms188427.aspx

Both OpenQuery and OpenRowset need "static" SQL to run. you can't pass a variable with SQL into it.

MrBungle - you COULD wrap the entire OPENQUERY/OPENROWSET in dynamic SQL in order to do this, and then use sp_ExecuteSQL to fire it. Or - you create the linked server, create a temp table with the right columns, and run something like

Insert #MyTempTable

Exec MyLinkedServer.MyDB.MyUser.MyStoredProc @var='123'

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 11

All right smarty....He's pointing at the BOL entry for OPENQUERY...

Heh... truth be told, I didn't even look once he said it was in Spanish.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)