Call or execute a Stored Procedure from Select Statement in SQL Server using OPENROWSET

Here We go :

When you try to execute queries involving OPENROWSET on SQL Server you get the following error:

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', see “Surface Area Configuration” in SQL Server Books Online.

This occurs basically when Ad Hoc Distributed Queries is disabled.

To enable this:

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

GO

EXEC sp_configure 'ad hoc distributed queries', 1

RECONFIGURE

GO

Now you can run the following query:

SELECT * FROM

   OPENROWSET('SQLNCLI','Server=(local);Trusted_Connection=Yes;Database=Northwind','EXEC [CustOrdersDetail] 10248')

Output will be:

Queso Cabrales                         14.00 12 0 168.00

Singaporean Hokkien Fried Mee 9.80  10 0 98.00

Mozzarella di Giovanni                 34.80 5 0 174.00

Note:

The data which comes from the procedure can also be filtered in the where clause.I tried with small example which is shown below:

SELECT * FROM

   OPENROWSET('SQLNCLI','Server=(local);Trusted_Connection=Yes;Database=Northwind','EXEC [CustOrdersDetail] 10248') where UnitPrice=14

After running the given above query the result would be like this:

Queso Cabrales 14.00 12 0 168.00

OPENROWSET To run with multi parameter:

SELECT * FROM

   OPENROWSET('SQLNCLI','Server=(local);Trusted_Connection=Yes;Database=Northwind','EXEC Employee_Sales_by_Country ''1996-08-01'' ,''1996-10-01''')