A stored procedure is not normally a table expression in a SELECT Statement. The question concerned rows from uspGetManagerEmployees.
-- Run in AdventureWorks2025; this target matches its inspected result metadata.
CREATE TABLE #ManagerEmployees
(RecursionLevel int NULL,
OrganizationNode nvarchar(4000) NULL,
ManagerFirstName nvarchar(50) NOT NULL,
ManagerLastName nvarchar(50) NOT NULL,
BusinessEntityID int NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL);
INSERT INTO #ManagerEmployees
(RecursionLevel,OrganizationNode,ManagerFirstName,ManagerLastName,
BusinessEntityID,FirstName,LastName)
EXEC dbo.uspGetManagerEmployees @BusinessEntityID=5;
SELECT RecursionLevel,OrganizationNode,ManagerFirstName,ManagerLastName,
BusinessEntityID,FirstName,LastName
FROM #ManagerEmployees ORDER BY RecursionLevel,OrganizationNode;
DROP TABLE #ManagerEmployees;This example uses the installed AdventureWorks2025 procedure for BusinessEntityID 5. Its temporary target matches all seven result columns. INSERT … EXEC and direct execution returned the same one-row result. Check the installed contract because sample releases can differ.
INSERT … EXEC has restrictions, including nested uses and compatible result shapes. An inline table-valued function can suit reusable relational logic. It is not automatically interchangeable with a procedure. Preserve side effects and calling behavior when redesigning.
My earlier OPENROWSET used the deprecated SQLNCLI provider. Current providers need deliberate connectivity, encryption and identity configuration. OPENROWSET opens a separate provider connection. It is not a local-session alias.
Don’t enable Ad Hoc Distributed Queries merely to remove error 15281. Its disabled state can be intentional. The matched local example avoids that feature and provider configuration. Use the least scope needed for the requirement.
Reference: OPENROWSET provider and configuration requirements.
Related reading
- Comprehensive Database Performance Health Check
- SQL in Sixty Seconds series
- Performance Troubles – Function on Date Variable – SQL in Sixty Seconds #192
- Performance: Between and Other Operators – SQL in Sixty Seconds #191
- Most Used Database Files – SQL in Sixty Seconds #190
- Optimize DATE in WHERE Clause – SQL in Sixty Seconds #189
- Data Compression for Performance – SQL in Sixty Seconds #188
- Get Current Time Zone – SQL in Sixty Seconds #187
- Detecting Memory Pressure – SQL in Sixty Seconds #186
- CPU Running 100% – SQL in Sixty Seconds #185
- Generate Script of SQL Server Objects – SQL in Sixty Seconds #184
- Prevent Unauthorized Index Modifications – SQL in Sixty Seconds #183
- MAX Columns Ever Existed in Table – SQL in Sixty Seconds #182
- Tuning Query Cost 100% – SQL in Sixty Seconds #181
A procedure result is not automatically a relational table source, it is a result contract to capture or redesign deliberately.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
8 Comments. Leave new
Can You include parámetros on it?
@Andry – Yes, at least for pass through queries run via Microsoft Access as the client, which use ODBC instead of OLE DB. I’m pretty sure you can also include parameters with an ADO command text, which does use OLE DB. I’d include some pictures, but it doesn’t look like I can with this comment.
I get this error when I try to run
Named Pipes Provider: Could not open a connection to SQL Server [2].
How can i pass parameter
ALTER PROC spTest
@EmpID INT
AS
BEGIN
SELECT *FROM (SELECT 1 EmpID,’Sayeed’ EmpName
UNION
SELECT 2 EmpID,’Runju’ EmpName
UNION
SELECT 3 EmpID,’Makhlesure’ EmpName
) A WHERE A.EmpID= @EmpID
END
GO
spTest 2
sp_configure ‘Show Advanced Options’, 1
GO
RECONFIGURE
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1
GO
RECONFIGURE
GO
DECLARE @EmpID INT
SET @EmpID=2
SELECT *
FROM OPENROWSET(‘SQLNCLI’,
‘server=192.168.97.12,1440;Database=AMBSKE;password= oLdViCtOrY2008;trusted_connection=yes;’,
‘exec [dbo].[spTest] 3’)
Thank you so much for the useful article Mr. Dave. Just one point. In the configuration code, “EXEC” is left out before sp_configure statement.
EXEC (or EXECUTE) is an optional keyword.
Ye, it works, but 2 will never be accepted as a “variable/parameter” ej: ‘exec Test.dbo.spTest @EmpID=@EmpID=’)
Using OPENROWSET has permission and security issues. It’s usually disabled with good reason. While you can insert into a table or table variable by executing a stored procedure, selecting directly from a stored procedure is not possible.