4

I'm using SQL Server 2005.

My stored procedure returns 100 columns and has 10 pages.

I'd only need to return 5 of the columns and don't want to duplicate the 10 pages of the stored procedure by creating a new stored procedure.

I'd like to avoid defining a new table variable with 100 columns! and I'd like to avoid defining a LinkServer and use OPENROWSET because the server name, etc shouldn't be hardcoded.

Is there any easier/better way?

If so, how to write it? The below code doesn't work:

select ID, Title, (the remaining 3 columns)
from exec dbo.sp_myName
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
The Light
  • 26,341
  • 62
  • 176
  • 258
  • 1
    You should write another stored procedure that only returns the 10 you need. It is possible via [`OPENROWSET`](http://stackoverflow.com/a/3005435/73226) though. – Martin Smith Sep 18 '12 at 13:47
  • thanks but I don't want to hardcode the server and database names. updated the question. I don't want to duplicate the stored procedure if possible. – The Light Sep 18 '12 at 13:54
  • possible duplicate of http://stackoverflow.com/questions/7417881/retrieve-a-specific-column-from-a-stored-proc-result-set-into-a-temp-table, although this question refers to obtaining only one column.... – David W Sep 18 '12 at 14:01

1 Answers1

1

You could create a temp table with all the columns that are returned by the stored procedure, and then use:

Insert Into #TempTable
Exec dbo.sp_myName

Select    ID, Title,...
From      #TempTable
Mike D
  • 186
  • 1
  • 8