I created a stored procedure to return a record containing about 60 columns. However, I would like to in another part of my project select from the same stored proc about 40 out of the 60 columns it returns. How do I properly specify which columns I need returned? In essence what are the rules, if any for
Insert into #TempTable ( . . . . . . .) Exec StoredProcedure
?
The rules are pretty simple. The select within the SP has to have as many columns as the #Temptable, and the columns, if not exactly the same type, must be compatible or coorcible, e.g. int going into VARCHAR.
If you have to select 40 out of 60 columns, you will have to pass it through 2 temporary tables, or use hacks involving the likes of OPENQUERY to treat the SP call as a remote dataset.
You can use many output parameters or one output parameter that is NVARCHAR(MAX). You could initialized it like:
'SELECT '+ Col01.Value + ','+ Col02.Value + ',' ... + ',' + Col40.Value
So, your store procedure will return something like:
'SELECT 10,50,''2012-10-02'',''Joro'''
And then you can do this:
DECLARE @OUTPUT NVARCHAR(MAX)='SELECT 10,50,''2012-10-02'',''Joro'''
EXEC sp_executesql @OUTPUT
Also, check this solution here:
