August 5, 2013 at 4:28 am
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]
August 6, 2013 at 9:13 pm
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
August 6, 2013 at 11:19 pm
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
August 6, 2013 at 11:47 pm
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 7, 2013 at 2:29 am
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]
August 7, 2013 at 10:51 am
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.
August 7, 2013 at 3:22 pm
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
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply