How to Combine Stored Procedure Result

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to Combine Stored Procedure Result

Author

Topic

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-25 : 04:15:17

below is the result that generate from 1st store prod (SP_1):

below is the result that generate from 2nd store prod (SP_2):

how can i use the T-SQL coding to combine it like this:

hope you all can guide me with simply code

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 04:26:01

create table #concat (uniqueid varchar(200), joborderno varchar(200), w int, completed int)

insert #concat
exec sp1

insert #concat
exec sp2

insert #concat
exec sp3

insert #concat
exec sp4

insert #concat
exec sp5

select * from #concat

drop table #concat

Peter Larsson
Helsingborg, Sweden

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-25 : 08:26:52

thanks again peso.

but in my case, me is not allow to create new table in database.

I only can add new SP in my database

thanks your advice

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-25 : 08:41:07

quote:
Originally posted by knockyo

thanks again peso.

but in my case, me is not allow to create new table in database.

I only can add new SP in my database

thanks your advice

Not even temp table ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-25 : 08:51:27

Not a best solution but how about using OPENQUERY()?

Select * from OPENQUERY(server-name, 'exec db1.dbo.sp_1')
union all
Select * from OPENQUERY(server-name, 'exec db1.dbo.sp_2')

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 09:28:44

Is he allowed to create linked servers, if he is not allowed to create tables?

Peter Larsson
Helsingborg, Sweden

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-25 : 09:35:57

quote:
Originally posted by khtan
Not even temp table ?

KH

ya, temp table also not allow

quote:
Originally posted by harsh_athalye
Not a best solution but how about using OPENQUERY()?

Select * from OPENQUERY(server-name, 'exec db1.dbo.sp_1')
union all
Select * from OPENQUERY(server-name, 'exec db1.dbo.sp_2')

about this code, is that can use in the SP to generate the result?

quote:
Originally posted by Peso
Is he allowed to create linked servers, if he is not allowed to create tables?

what is linked servers? i think i has a way is to create store procedure only

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 09:50:47

You think!?!?

Have you even tried the suggestions posted to you here?
If not, please do that and return with valuable feedback.

Peter Larsson
Helsingborg, Sweden

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-25 : 10:00:57

Peso,

I sure i only can add new SP into my DB, other than that cannot already....

ok, i will try "harsh_athalye" suggestion and let you all know my feedback....

thanks

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-25 : 19:31:41

Select * from OPENQUERY(server-name, 'exec db1.dbo.sp_1')
union all
Select * from OPENQUERY(server-name, 'exec db1.dbo.sp_2')

in that "server-name" i need to put the Linked-server, how i need to create LinkedServer?

in my case now, i just only allow to using Stored Prod to do the combination.

Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-25 : 19:53:49

You will not be able to create a linked server.

Just create the temp table like Peter said. You are allowed to create it.

CODO ERGO SUM

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-25 : 19:59:28

quote:
Originally posted by Michael Valentine Jones

You will not be able to create a linked server.

Just create the temp table like Peter said. You are allowed to create it.

CODO ERGO SUM

thanks Michael.

Actually I cannot add the new Table, even Temp Table I also not allow to do so.

So, tht's why i need to use other method to run my combination of this two SP.

If like that, in VB.net code, can it be done combination? I really hope that SP can do combination, then my VB.net code no need be troubleshoot.

Thanks you all again.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-25 : 20:23:50

I don't understand. What's wrong with using temp table ? What is the concern ? Temp table only exists within the scope.

Maybe you can tell us what you are allow to do before we post any further suggestions ?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-25 : 20:24:37

Did you even try to create the temp tables? There isn't really a way they can stop you. Do you even know what a temp table is?

CODO ERGO SUM

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-25 : 20:36:12

create table Atest (uniqueid varchar(200), [Lot Id] varchar(200), [Lot Size] int, [Plan Date] varchar(40) ,
[Actual Date] varchar(40), [Current Step] varchar(100))

insert Atest
exec rpt_Daily_AP_Child

insert Atest
exec rpt_Daily_AP_Child

select * from Atest

drop table Atest

You mean this coding? Oops, i am just trying.
Actually this code is to create a new table in database, after generate all the information i want, it will auto drop the table itself? is that means?

I thought the temp table will always display on the database and cannot be delete anymore.

Thanks you all advice. Maybe I am quite stupid

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-25 : 20:42:44

As I suspected, you have no idea what a temporary table is.

Read about them in this topic from SQL Server Books Online:
Creating and Modifying a Table
http://msdn2.microsoft.com/en-us/library/aa933114(SQL.80).aspx

CODO ERGO SUM

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-25 : 21:01:49

quote:
Originally posted by Michael Valentine Jones

As I suspected, you have no idea what a temporary table is.

Read about them in this topic from SQL Server Books Online:
Creating and Modifying a Table
http://msdn2.microsoft.com/en-us/library/aa933114(SQL.80).aspx

CODO ERGO SUM

Thanks Michael information again and also whose contribute their time help me to solve the above problems.

Really appreciate.

knockyo
Yak Posting Veteran

83 Posts

Posted - 2007-01-28 : 02:10:19

if following the above CODE, i already create the temporary table in my database.

let's say during my coding (vb.net) have happen some errors or time out. Then, is that possible happen i cannot retrieve the data from that Temporary table anymore?

----------------
**Because once you create temp table, it will appear automatically at the database, it need to restart database, then the table will disappear itself.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources