How to Filter Column Returned from Stored Procedure SQL

I was asked by my Team Lead a task about Stored Procedures in SQL Server, the requirement was like

We had a Stored Procedure which returns a Table and that table has about 80 columns. Now in some cases we only want some of the columns not all.

I was not allowed to alter the Stored Procedure in any way, so the only thing I could try is at the point of calling the SP.

So I had some research over this, and I found a SQL function named OPENROWSET

This Function accept three parameters

 1. Provider Name
 2. Connection String
 3. Stored Procedure to be executed

In order to make this method work we have to enable the ad hoc Query, so execute the following query.

EXEC sp_configure
  'show advanced options',
  1 RECONFIGURE
  go
 EXEC sp_configure
  'ad hoc distributed queries',
  1 RECONFIGURE 

Lets First create a table

go

CREATE TABLE test
  (
     id   INT,
     NAME VARCHAR(10),
     age  INT,
  ) 
Now we will create a Stored Procedure which will return a datatable of table Test

go

CREATE PROC Getdata
AS
  BEGIN
      SELECT *
      FROM   test
  END 

Now if we try to execute the above SP it will return all the columns contained in the Test table, in our case we only want ID and Name columns. So here's how we do it

SELECT id,
       NAME
FROM   OPENROWSET ('SQLOLEDB',
                   'Server=(local);TRUSTED_CONNECTION=YES;',
                   'EXEC master.dbo.Getdata') AS tbl 

This way you will get only ID and Name columns

I hope that This tutorial was informative for you and I would like to thank you for reading.