How to call or execute a Stored Procedure from inside a Select Statement

How to call or execute a Stored Procedure from Select Statement in SQL Server?

It is really simple to call a stored procedure from a select statement Using OPENROWSET.

Below TSQL Query calls the procedure sp_who from the select statement.

[sql]
SELECT * FROM
OPENROWSET(‘SQLNCLI’
,’Server=(local);Trusted_Connection=Yes;Database=Master’
,’EXEC dbo.sp_Who’)
[/sql]

Syntax:
[sql]
SELECT * FROM
OPENROWSET(‘SQLNCLI’
,’Server=(local);Trusted_Connection=Yes;Database=Master’
,’EXEC [procedurename]’)
[/sql]

Additional Information:
The data that is coming from the procedure can also be filtered in the where clause.

[sql]
SELECT * FROM
OPENROWSET(‘SQLNCLI’
,’Server=(local);Trusted_Connection=Yes;Database=Master’
,’EXEC dbo.sp_Who’)
where dbname = ‘master’
[/sql]

The data from the execution of a select statement can be directly pushed into a table.

[sql]
SELECT * INTO who2_table FROM
OPENROWSET(‘SQLNCLI’
,’Server=(local);Trusted_Connection=Yes;Database=Master’
,’EXEC dbo.sp_Who’)
where dbname = ‘master’
[/sql]

If the openrowset is disabled in the system, learn how to enable it using this link enable openrowset

Related to SQL Server 2005,SQL Server 2008,SQL Server 2012

2 thoughts on “How to call or execute a Stored Procedure from inside a Select Statement

  1. SELECT * FROM OPENROWSET(‘SQLNCLI’
    ,’Server=(local);Trusted_Connection=Yes;Database=Master’
    ,’exec dbo.sp_getNewID A,13,@id)’)

    Getting below error
    Msg 15281, Level 16, State 1, Line 1
    SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.

Leave a Reply

Your email address will not be published. Required fields are marked *