Top from store procedure

Question

Post reply

Top from store procedure

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

Stefan_G (8/5/2013)
Why is this such a bad solution?

See discussion in my artlce: http://www.sommarskog.se/share_data.html#OPENQUERY

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

ck9663 Right there with Babe Points: 763 More actions · Answer 2

How about this?

declare @spWhoTable as table

(

col_spid int,

col_ecid int,

col_status varchar(20),

col_loginame varchar(20),

col_hostname varchar(20),

col_blk varchar(20),

col_dbname varchar(20),

col_cmd varchar(50),

col_request_id int

)

insert into @spWhoTable

exec sp_who

select

top 5

*

from @spWhoTable

Happy Coding!!!

~~ CK

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

Erland Sommarskog (8/5/2013)
Beware that this is a very bad solution. If were to meet in a code review I would never approve of this solution. Did you read the article I pointed you to?

What would you use as an alternative? Insert/Exec?

I also agree that I'd raise a curious eye during a code review as to why the output of a stored procedure was basically being relegated to RBAR.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246979 More actions · Answer 4

Jeff's original suggestion (optional parameter to the stored procedure) really is the way to go here. I can't understand why you would choose the convoluted alternative over it.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 5

Jeff Moden (8/6/2013)

What would you use as an alternative? Insert/Exec?

If you've read my article, you know that nor am I fond of INSERT/EXEC.

As long as I have control over the procedure I am calling, I would look at a solution with sharing a temp table or similar. Or in this case, suggested by others, add a parameter to the stored procedure. However, we don't know the full context for the question.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

ck9663 Right there with Babe Points: 763 More actions · Answer 6

I agree.

I made the INSERT/EXEC recommendation with the assumption that the he does not have any control on the SP aside from executing it.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 7

Erland Sommarskog (8/7/2013)

Jeff Moden (8/6/2013)
What would you use as an alternative? Insert/Exec?
If you've read my article, you know that nor am I fond of INSERT/EXEC.
As long as I have control over the procedure I am calling, I would look at a solution with sharing a temp table or similar. Or in this case, suggested by others, add a parameter to the stored procedure. However, we don't know the full context for the question.

It's a long article and I admittedly didn't read it from sternum to sox. 😛 I do absolutely agree that if it's something that can be modified (my first suggestion in the second post of this thread) by adding an optional parameter that defaults to "all", then that should be done instead of using the kludge of OPENROWSET or INSERT/EXEC.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)