Selecting columns from a stored Proc?

Question

I have a stored proc and I am trying to select all rows from it.

SELECT * FROM dbo.SEL_My_Func 'arg1','arg2','ar3'

didnt work. So I also tried:

SELECT * FROM EXEC dbo.SEL_My_Func 'arg1','arg2','ar3'

but this also didnt work. How do I get to test my stored proc returns correct results?

I have had to use a proc, rather than a function because I have an ORDER BY as part of the SQL, see: Selecting first row per group

Lastly, am I right in thinking there is no problem limiting which columns are returned from the stored probc, you just cant specify which rows (otherwise you would be better using a SQL function)?

You can't compose the output of a stored proc within another statement - you can't restrict which columns and rows are produced - short of dumping the entire output in a temp table, and then `SELECT`ing from there - but then you need to apply the `ORDER BY` to that `SELECT` — Damien_The_Unbeliever, Jun 07 '12 at 12:07

score 3 · Answer 1 · answered Jun 07 '12 at 12:06

3

The solution you are using qwill not work:

workarounds are there

SELECT a.[1], a.[2]
FROM OPENROWSET('SQLOLEDB','myserver';'sa';'mysapass',
    'exec mydatabase.dbo.sp_onetwothree') AS a

or split your task in two queries

Declare @tablevar table(col1,..
insert into @tablevar(col1,..) exec MyStoredProc 'param1', 'param2'

SELECT col1, col2 FROM @tablevar

answered Jun 07 '12 at 12:06

Romil Kumar Jain

19,561
8
59
90

However, the OP's whole reason for placing their code within a stored proc was so that they could specify an `ORDER BY`. The second definitely isn't guaranteed to respect the order of rows returned by the stored proc in the final output. I don't think the first one is either. – Damien_The_Unbeliever Jun 07 '12 at 12:16
@Damien, the order by wasn't in the normal sense. The stored proc returns an un-orded set of results. However, these results had already been ordered by on a column which was grouped by (probably best you see the link i included at the top) – mezamorphic Jun 07 '12 at 12:30
@Damien_The_Unbeliever, OP can use the order by clause with the approaches I have shown in the post. – Romil Kumar Jain Jun 07 '12 at 12:52

score 1 · Accepted Answer · answered Jun 07 '12 at 12:05

1

EXEC dbo.SEL_My_Func 'arg1','arg2','ar3'

answered Jun 07 '12 at 12:05

juergen d

195,137
36
275
343

Thank you. Does this mean I cannot only pick a subset of the proc columns to return? – mezamorphic Jun 07 '12 at 12:06
See Romil's solution for that or [here](http://stackoverflow.com/questions/209383/select-columns-from-result-set-of-stored-procedure) – juergen d Jun 07 '12 at 12:08

score 0 · Answer 3 · answered Jun 07 '12 at 13:56

Your assumption about functions is incorrect. You can use partition functions to select the first row in group.

Here is an example to find the first dealer_id for each client:

select client, dealer_id
from (
     select client, dealer_guid
         , RANK() over ( partition by client order by dealer_id) as rnk
     from Dealers
) cd where rnk = 1

This can also be done with a function call as well as with a table (my example).

Selecting columns from a stored Proc?

3 Answers3