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
Here is how tsql developers can call a sql stored procedure and execute sp using EXEC or EXECUTE command.
'7141234567' is a random phone number just used for the sql sample code.
EXEC Customers_SearchByPhone '7141234567'
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
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.
It is important that the SQL Server linked server must already defined in the SQL Server Objects Linked Servers list.
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
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
And the select from sql stored procedure output is as follows for this sample.
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.
|