SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


How to SQL Select from Stored Procedure using SQL Server OPENQUERY or OPENROWSET

SQL developers frequently require to select from sql stored procedure execute results.
This t-sql tutorial will display a sample t-sql script using SQL Server OPENQUERY which show how to sql select from SQL Server stored procedure.
The second example will be similar to SQL OpenQuery but this time in sql codes we will use SQL OPENROWSET in order to select from stored procedure.

The alternative method of using select from sql stored procedure is to use temp tables, using SELECT INTO or INSERT INTO statements with SQL Server stored procedures.
In MS SQL Server 2005 and MS SQL Server 2008 databases, SQL Server administrators and t-sql developers can implement all these solutions easily.



SQL Server Stored Procedure

Here is a sample SQL Server stored procedure which will be used in the example for sql select from procedure.
This stored procedure or SP will return all customer information and related customer address detail whose phone number is given as a parameter argument to the SQL Server stored procedure.

CREATE PROCEDURE Customers_SearchByPhone (
  @phone nvarchar(15)
)
AS
SELECT c.*, a.*
FROM Customers c
INNER JOIN Addresses a
  ON c.AddressId = a.AddressId
WHERE c.phone = @phone
GO
Code

Here is how tsql developers can call a sql stored procedure and execute stored procedure using EXEC or EXECUTE command.
'7141234567' is a random phone number just used for the sql sample code.

EXEC Customers_SearchByPhone '7141234567'
Code

SQL SELECT from Stored Procedure using OPENQUERY

Let's now execute the sample sql stored procedure, then join the result set with an other sql database table States. Write sql select from stored procedure code and join sql table using T-SQL OPENQUERY command.

SELECT query.CustomerId, query.FirstName, States.*
FROM OPENQUERY([.], 'MySQLSamples.dbo.Customers_SearchByPhone ''7141234567''') query
INNER JOIN States ON query.Code = States.Code
Code

SQL programmers can use the above OPENQUERY syntax and OPENQUERY structure for other sql examples.

Note that the "[.]" is the linked server name which is pointing to the current SQL Server database instance. This is called SQL Server loopback linked server.
It is important that the SQL Server linked server must already defined in the SQL Server Objects Linked Servers list.
For creating linked server please refer to How to Create Microsoft SQL Server Linked Server tutorial.

SQL Server linked servers

If the sql linked server name is not used in the correct form, and with the correct link server name within the t-sql OpenQuery statement, as follows SQL engine will throw exception.

SELECT *
FROM OPENQUERY([kodyaz], 'MySQLSamples.dbo.ListStates') oq
INNER JOIN States s ON oq.Code = s.Code
Code

And here is the tsql OpenQuery error related with false linked server name usage.

Msg 7411, Level 16, State 1, Line 1
Server 'kodyaz' is not configured for DATA ACCESS.


SELECT from SQL Stored Procedure using OPENROWSET

Here is an t-sql OpenQuery example used to select from stored procedure.
Note that this time in this sql tutorial, we will be using OPENROWSET instead of OPENQUERY in order to select from a stored procedure result set.

SELECT c.CustomerId, c.FirstName, s.*
FROM OPENROWSET('SQLNCLI', 'Server=kodyaz;Trusted_Connection=yes;',
  'EXEC DBName.dbo.ListCustomersByPhone ''7419990777'', 0 ') AS c
INNER JOIN States s ON c.StateDescription = s.Code
Code

And the select from sql stored procedure output is as follows for this sample.

select from stored procedure using openrowset

I hope developers will like this alternative solution for SQL Server select from stored procedure and will use in their database development and production environments.

If you need to select from stored procedure in a SQL function, then please review the following SQL tutorial Select from Stored Procedure in SQL Function using OpenQuery



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.