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

[Originally posted by]: https://www.toadworld.com/platforms/sql-server/b/weblog/archive/2012/05/09/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.

SELECT * FROM 
   OPENROWSET('SQLNCLI'
              ,'Server=(local);Trusted_Connection=Yes;Database=Master'
              ,'EXEC dbo.sp_Who')

Syntax:

SELECT * FROM 
   OPENROWSET('SQLNCLI'
              ,'Server=(local);Trusted_Connection=Yes;Database=Master'
              ,'EXEC [procedurename]')

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

SELECT * FROM 
   OPENROWSET('SQLNCLI'
              ,'Server=(local);Trusted_Connection=Yes;Database=Master'
              ,'EXEC dbo.sp_Who')
where dbname = 'master'

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

SELECT * INTO who2_table FROM 
   OPENROWSET('SQLNCLI'
              ,'Server=(local);Trusted_Connection=Yes;Database=Master'
              ,'EXEC dbo.sp_Who')
where dbname = 'master'

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: