CODEWITHSUNDEEP
sql-servert-sqlvariablesstored-procedures
The Light
The Light

Reputation: 27021

How to put the result of a stored procedure into a table variable?

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

Upvotes: 4

Views: 7621

Answers (1)

Mike D
Mike D

Reputation: 196

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

Upvotes: 1

Related Questions