Calling an SP from a Select Statement

Question

Calling an SP from a Select Statement

andreask

Mr or Mrs. 500

Points: 542
More actions
November 29, 2004 at 3:45 am

#165586

Dear All,
I have a SP which returns let's say 3 values:
Table "A" has the following fields:
Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10
"Exec dbp.My_SP A.Field1, A.Field2" will return @Value1, @Value2 and @Value3.
How do I get these 3 values along with other fields of the same table in a single query?
Thanks,
Andreas K.

Viewing 4 posts - 1 through 3 (of 3 total)

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

I would research using an in-line function. You cannot call a stored-procedure from a SELECT but can call functions. The BOL has decent information on UDFs

Good Hunting!

AJ Ahrens

webmaster@kritter.net

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

Perhaps instead of a stored procedure, it should be a view so that you can select from it as if it were a table. Also, take a look at OpenRowSet in Books-on-Line to see how to use the results of a stored procedure as if it were a table... the disadvantage is that a user login and password must be provided in "clear code" which is not usually a good idea.

--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)

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 3

I agree with AJ Ahrens above -- a table-valued function is probably your best bet. But if you must used a stored procedure, you can use Openquery instead of Openrowset to solve some of the security issues.

select * from Openquery(<myServer>, 'exec db.owner.storedProc <parm1>, <parm2>, ...')

Note the following:

1) Must set data access to allow a "distributed" query from a server to itself: sp_serveroption '<myServer>', 'data access', true

2) You probably need to specify the full path to the stored proc, since the logon may not go to your particular database.

3) Depending on what's happening inside the stored proc, I've found the need to specify "SET FMTONLY OFF" just prior to the proc name, Openquery(<myServer>, ' SET FMTONLY OFF exec db.owner.storedProc <parm1>, <parm2>, ...'). This seem especially true if you create temporary tables within the proc. Simple selects don't need this.

Make sure you have a primary key or other identifier coming out of the stored proc, so you can join back to your original table to combine columns as you would like.

Hope this helps,

Scott Thornburg

Volt Information Sciences