SQL Server Portal

In most of the applications, we create some multipurpose stored procedure that we use for multiple forms, reports, exports, etc. The challenge we usually face with this kind of stored procedure is that whatever columns are available in the result set, stored procedure will return the same number of columns as an output and not the selected/required information.

Let me take a sample stored procedure from AdventureWorks2012 database and view its result set.

USE AdventureWorks2012
GO
EXEC [dbo].[uspGetManagerEmployees] 16
GO
--OUTPUT

As you can see that a number of columns are available in the above stored procedure result set but I need to select [BusinessEntityID], [FirstName], [LastName] ONLY.

Given below are the two methods to achieve it.

Method 1 :
In this method, we need to follow three steps :

Step 1:
Create a temporary table according to the number of columns in the result set of the stored procedure.

USE AdventureWorks2012
GO
CREATE TABLE #TEMP
(
[RecursionLevel] INT
, [OrganizationNode] VARCHAR(50)
, [ManagerFirstName] NVARCHAR(50)
, [ManagerLastName]  NVARCHAR(50)
, [BusinessEntityID] INT
, [FirstName] NVARCHAR(50)
, [LastName] NVARCHAR(50)
)
GO

Step 2:
Insert the complete result set of the stored procedure in the table.

USE AdventureWorks2012
GO
INSERT INTO #TEMP EXEC [dbo].[uspGetManagerEmployees] 16
GO

Step 3:
Select the required columns from the temporary table.

USE AdventureWorks2012
GO
SELECT
[BusinessEntityID]
, [FirstName]
, [LastName]
FROM #TEMP
GO
--OUTPUT

Method 2 :
In this method, we need to use OPENROWSET and can select the column of the stored procedure result set directly.

SELECT
[BusinessEntityID]
, [FirstName]
, [LastName]
FROM OPENROWSET('SQLNCLI', 'Server=(local);uid=test;pwd=test',
'EXEC AdventureWorks2012.[dbo].[uspGetManagerEmployees] 16') ;
GO
--OUTPUT

Read Full Post »