Executing stored procedure within SELECT statement

Question

Executing stored procedure within SELECT statement

shahm10

SSCrazy

Points: 2217
More actions
January 12, 2010 at 2:17 pm

#141601

Is there any way of executing following stored procedure from SELECT statement?
for eg:
SELECT 'EXEC SP_COLUMNS ' + name
FROM sys.objects WHERE TYPE='U'
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

I'm not entirely sure what you're trying to accomplish here. Is this intended to create a list of columns to select from a table?

If so, you can do that through dynamic SQL.

If not, what is the desired end result here?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

shahm10 SSCrazy Points: 2217 More actions · Answer 2

That was just an example. I just wanted to find out whether i can call stored procedure from SELECT statement or not.

Thanks

GSquared SSC Guru Points: 260824 More actions · Answer 3

Nope. Not really.

If you want to use a stored procedure in the From clause, I believe that can be done with OpenRecordset, but I have to admit, I never got that to work, and just insert into temp tables using Insert Exec and then use the temp table in the From clause.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Paul White SSC Guru Points: 150467 More actions · Answer 4

You can use a table-valued function (TVF) in a SELECT, so if the logic can be moved from the procedure to a TVF that might work. You can't call a stored procedure from a T_SQL TVF directly. It is possible from a CLR TVF, but I don't recommend it.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Lowell SSC Guru Points: 323518 More actions · Answer 5

like GSquared said, it's possible, and substantially slower too.

here is a simple example, which just runs sp_who; change your server name and this should work:

SELECT *

FROM OPENROWSET('SQLOLEDB','Server=stormdev\SQLExpress;Trusted_Connection=Yes;Database=Master',

'Set FmtOnly OFF; EXEC dbo.sp_Who')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Paul White SSC Guru Points: 150467 More actions · Answer 6

Something else to bear in mind about the OPENROWSET method:

Books Online
OPENROWSET can be used to access remote data from OLE DB data sources only when the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider, and the Ad Hoc Distributed Queries advanced configuration option is enabled. When these options are not set, the default behavior does not allow for ad hoc access.
When accessing remote OLE DB data sources, the login identity of trusted connections is not automatically delegated from the server on which the client is connected to the server that is being queried. Authentication delegation must be configured. For more information, see Configuring Linked Servers for Delegation.
Catalog and schema names are required if the OLE DB provider supports multiple catalogs and schemas in the specified data source. Values for catalog and schema can be omitted when the OLE DB provider does not support them. If the provider supports only schema names, a two-part name of the form schema.object must be specified. If the provider supports only catalog names, a three-part name of the form catalog.schema.object must be specified. Three-part names must be specified for pass-through queries that use the SQL Server Native Client OLE DB provider. For more information, see Transact-SQL Syntax Conventions (Transact-SQL).
OPENROWSET does not accept variables for its arguments.

Enabling the Ad Hoc Distributed Queries option may not be desirable or possible in some environments.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi