Is there anyway to select specific columns from the output of SP?

• suneel-210238

  SSC Enthusiast

  Points: 193

  More actions

  June 17, 2009 at 7:43 pm

  #93586

  Hi,

  I have a SP that returns 10 columns. I got a requirement that only few columns need to be returned from that SP.

  Just a note here the column names and Sps are dynamic.

  I can do it by converting this into a function and select a sub-set of columns but I have around 50 Sps that need to be converted.

  Please help!

  thank you in advance

  -Suneel

• WayneS

  SSC Guru

  Points: 95460

  More actions

  June 17, 2009 at 7:51 pm

  #1011378

  Not directly, but there is an indirect way.

  Create a temp table with columns that match the data types of the columns being selected in the procedure.

  Then:

  insert into #MyTempTable

  execute dbo.MyProc

  Then just select the columns you need.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• hi_abhay78

  SSCertifiable

  Points: 6265

  More actions

  June 17, 2009 at 10:15 pm

  #1011420

  small repro for you based on above answer by wayne :

  step 1 : create table test (a int,b int,c int)

  Step 2: insert into test values (2,2,2)

  go 10

  step 3: create proc test1 as select * from test

  step 4: create table #MyTempTable (a int,b int,c int)

  step 5: insert into #MyTempTable exec test1

  step 6: create table #MyTempTable1 (a int,b int)

  step 7: insert into #MyTempTable1 select a,b from #MyTempTable

  step 8:select * from #MyTempTable1

  Abhay Chaudhary
  Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

• Tom257

  SSChampion

  Points: 11543

  More actions

  June 18, 2009 at 2:41 am

  #1011520

  Another possibility is to create a table valued function instead of a stored procedure.

  -- simple example as a stored procedure

  CREATE PROC MyProc

  @PARAM INT

  AS

  SELECT TOP(@PARAM) *

  FROM sys.syscolumns;

  GO

  EXEC MyProc 20;

  GO

  -- simple example as a function

  CREATE FUNCTION MyFunc(@PARAM INT)

  RETURNS TABLE

  AS

  RETURN SELECT TOP(@PARAM) *

  FROM sys.syscolumns;

  GO

  SELECT name, id

  FROM MyFunc(20)

  You can then join the output of your function to another table if required.