October 31, 2011 at 9:39 am
I need to pull data using multiple EXECs to get individual items. Example:
EXEC Rpt_ItemWhereUsedSpTest @StartingItem = '2P17107', @EndingItem = '2P17107'
EXEC Rpt_ItemWhereUsedSpTest @StartingItem = '2P17110', @EndingItem = '2P17110'
EXEC Rpt_ItemWhereUsedSpTest @StartingItem = '2P17115', @EndingItem = '2P17115'
EXEC Rpt_ItemWhereUsedSpTest @StartingItem = '2P17852', @EndingItem = '2P17852'
EXEC Rpt_ItemWhereUsedSpTest @StartingItem = '2P17857', @EndingItem = '2P17857'
EXEC Rpt_ItemWhereUsedSpTest @StartingItem = '2P17855', @EndingItem = '2P17855'
Obviously, the output will be in multiple windows. Is there any sort of command such as UNION ALL that will allow just one output?
Thanks.
Steve
October 31, 2011 at 9:44 am
Sure is. Declare a table variable to hold the results. Then
insert into @Table
exec sp_xxxxx
After that, you can select from your table variable as normal.
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
October 31, 2011 at 11:32 am
If you don't want to predefine the table you can use something like this:
declare @iParm int
set @iParm = 111249972
declare @sql varchar(1000)
set @sql = 'SELECT a.* into ##tmpAEC FROM OPENROWSET(''SQLNCLI'',''Server=devsql\tpa1;Trusted_Connection=yes;Database=Manhattan'',''exec TysonTest ' + convert(varchar,@iParm) + ''') as a'
exec(@sql)
select @iParm = 111249972
set @sql = 'insert ##tmpAEC select * FROM OPENROWSET(''SQLNCLI'',''Server=devsql\tpa1;Trusted_Connection=yes;Database=Manhattan'',''exec TysonTest ' + convert(varchar,@iParm) + ''') as a'
exec(@sql)
select * from ##tmpAEC
drop table ##tmpAEC
October 31, 2011 at 12:50 pm
tyson.price (10/31/2011)
If you don't want to predefine the table you can use something like this:declare @iParm int
set @iParm = 111249972
declare @sql varchar(1000)
set @sql = 'SELECT a.* into ##tmpAEC FROM OPENROWSET(''SQLNCLI'',''Server=devsql\tpa1;Trusted_Connection=yes;Database=Manhattan'',''exec TysonTest ' + convert(varchar,@iParm) + ''') as a'
exec(@sql)
select @iParm = 111249972
set @sql = 'insert ##tmpAEC select * FROM OPENROWSET(''SQLNCLI'',''Server=devsql\tpa1;Trusted_Connection=yes;Database=Manhattan'',''exec TysonTest ' + convert(varchar,@iParm) + ''') as a'
exec(@sql)
select * from ##tmpAEC
drop table ##tmpAEC
How can that possibly work if you have more than 1 user running this code at the same time?
You'd need to uniquify the tablename somehow.
November 1, 2011 at 4:13 am
I didn't see that being a requirement...where do you see that?
It wasn’t this:
“I need to pull data using multiple EXECs to get individual items. Example:”
Or this:
“Obviously, the output will be in multiple windows. Is there any sort of command such as UNION ALL that will allow just one output?”
I took it as some need that a programmer had. I can’t think of a good reason you would to do that from an application.
November 1, 2011 at 4:30 am
tyson.price (11/1/2011)
I didn't see that being a requirement...where do you see that?It wasn’t this:
“I need to pull data using multiple EXECs to get individual items. Example:”
Or this:
“Obviously, the output will be in multiple windows. Is there any sort of command such as UNION ALL that will allow just one output?”
I took it as some need that a programmer had. I can’t think of a good reason you would to do that from an application.
The point is you don't know for sure. And for any application I've ever seen it's always multiple users by default unless proven otherwise.
Just change the sp name to sp_updateprice or sp_calculate_inventory and it's obvious this needs to run multiple users at the same time.
I'm not judging you nor the answer. I know it works, but there's a big caveat in it! Those folks finding this thread on google need to know about it before it's too late.
November 1, 2011 at 4:44 am
"The point is you don't know for sure."
Actually you made that leap to multiple users not me, I took it at face value. Why would any application possibly string together statements like that? To me, without clarification from the OP, I provided what was asked for. I would pity the fool, and his\her users, that would put what I posted into an application. For them it’s already too late if they try something like that and they should be using Google to find a different career. 😀
I actually posted that same question as the OP but I didn’t want to define the tables. Someone here posted the solution I provided, I can’t take credit for it.
November 1, 2011 at 4:50 am
Certainly not the most obvious way to code this... but the fact is you still don't know.
Since 99.9999% of the systems need to work into multiple users mode, it's more a rule than a leap in my world.
November 1, 2011 at 4:58 am
OK...let's agree to disagree. I see something like that, no good reason for something like that in a multi-user environment, and I take it to be a programmer's request for some knd of tool.
We should stop this line of discussion in case someone does use Google and wonders why we are bothering 🙂
November 1, 2011 at 7:43 am
Personally (if at all possible, I don't know the systems you are working with) I would look at making the stored procedure Rpt_ItemWhereUsedSpTest output what you want in the first place.
Can you pass the parameters into the sproc as a table variable as oposed to calling the sproc multiple times? It may well be a terrible idea in your situation but worth thinking about.
Whenever I find myself trying to do something that isn't 100% obvious as to how to fix I tend to step back and look again at what I'm trying to acheive.
At any rate, the best solution by far is Phil's suggestion of using insert-exec. This can either be achieved with a table variable, temp table, global temp table or a permanent/physical table. Unless of course you are using insert-exec in Rpt_ItemWhereUsedSpTest in which case it wont nest.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
November 1, 2011 at 9:11 am
Sorry, folks, I didn't mean to cause a stir like this. However, I will learn from this and make sure I specify whether ir not it is for multiple users or one lonely programmer. In this case, it is the latter.
I appreciate all the help all of you have given me. I will work with what you have given me and incorporate the one that works best in our environment.
Regards,
Steve
November 1, 2011 at 9:17 am
sdownen05 (11/1/2011)
Sorry, folks, I didn't mean to cause a stir like this. However, I will learn from this and make sure I specify whether ir not it is for multiple users or one lonely programmer. In this case, it is the latter.I appreciate all the help all of you have given me. I will work with what you have given me and incorporate the one that works best in our environment.
Regards,
Steve
No need to apologise Steve, there was nothing wrong with your post. Hope you find something useful amongst the chit-chat 😉
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply