June 4, 2012 at 2:14 am
i have an SP which returns 20 columns.
I need to retrieve 10th column value.
is it possible.
Note:
1. i dont want to use a temp table for all those 20 columns and read my 10th.
2. i dont want to use OUTPUT clause within SP, as i might be restricted to alter SP.
Appreciating your help always.
June 4, 2012 at 8:53 am
In order to retrieve the output of a select in a stored proc you have to first insert it somewhere. A temp table is the most logical place from what you are describing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 4, 2012 at 9:09 am
If you want to avoid using a temp table, you could use a table variable.
June 4, 2012 at 9:20 am
Change the SP or create a new one.
Jared
CE - Microsoft
June 4, 2012 at 11:01 am
Hi
You have more ways. It's one right now on my mind. You could put your code, which selects the 20 rows, in a CTE and with an index for each of the rows. Then you will add one more select with WHERE condition for the 10th row.
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
June 4, 2012 at 11:14 am
IgorMi (6/4/2012)
HiYou have more ways. It's one right now on my mind. You could put your code, which selects the 20 rows, in a CTE and with an index for each of the rows. Then you will add one more select with WHERE condition for the 10th row.
Regards,
IgorMi
Well that would work but it negates the OP using the SP they already have. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 4, 2012 at 12:40 pm
Is it your proc? Does it need to exist, and if so do you need to preserve it's interface because it has other uses? What is the proc doing? Depending on what the proc does you could move the code into a Table-valued Function (TVF), refactor the procedure to call the TVF and use the TVF in your code to retrieve the 10th column into a variable.
Rewriting Stored Procedures as Functions
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply