Sunday, November 29, 2015

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',

 EXEC sp_configure
  'ad hoc distributed queries',

Lets First create a table


     id   INT,
     NAME VARCHAR(10),
     age  INT,

Now we will create a Stored Procedure which will return a datatable of table Test


      SELECT *
      FROM   test

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

                   '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.

No comments:

Post a Comment