SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 how to combine two tables in stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 06/28/2005 :  10:41:53  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Vishwa Rao writes "I have two select statements giving different data sets. So I cannot use UNION.
I need to concatenate them and display in a asp.net datagrid. I thought I can do something like this:
Store results of select statements in two seperate tables. Then concatenate the rows from tbl1 and tbl2 to generate a new table tbl3. It does not work. Here is the stored procedure code. I am new to stored procedure - my second day! I would appreciate corrections or suggestions.

CREATE PROCEDURE dbo.ResourceDetailsGivenEventId_sp
(
@eid varchar(50)

)
AS
declare @type varchar(50)
declare @srcid varchar(50)
declare @ch varchar(100)
declare @sla integer
declare @service varchar(100)


Declare @tbl1 table (RowId int identity(1,1), ResourceId varchar(100), ResouceDescription varchar(100), ResourceLocation varchar(100), ResourceDetails varchar(100))
Declare @tbl2 table (RowId int identity(1,1), ChannelDescription varchar(100), DescriptionDescription varchar(100), ServiceContact varchar(100))
Declare @tbl3 table (RowId int identity(1,1), ResourceId varchar(100), ResouceDescription varchar(100), ResourceLocation varchar(100), ResourceDetails varchar(100), ChannelDescription varchar(100), DescriptionDescription varchar(100), ServiceContact varchar(100))


Select @sla=slaid, @srcid=EventSourceId, @type=EventSourceGeneric from dbo.tEvent where EventId=@eid
Select @service=ServiceId, @ch=ChannelId from dbo.tSLA where SLAID=@sla

if @type='Device'
insert @tbl1
Select ResourceID, ResourceDescription, ResourceLocation, ResourceDetails from dbo.tResource where ResourceIP=@srcid

insert @tbl2
Select tChannel.ChannelDescription, tService.Description, tService.ServiceContact from dbo.tChannel LEFT JOIN dbo.tService ON tChannel.ServiceId=tService.Serviceid where tChannel.ChannelId=@ch

insert @tbl3
select @tbl1.ResourceDescription, @tbl2.ChannelDescription from @tbl1 left join @tbl2 ON @tbl1.RowId=@tbl2.RowId
select * from tbl3

GO"

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 06/29/2005 :  00:47:22  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Can you give the sample data with the result of how you want to concatenate?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 06/29/2005 :  01:34:45  Show Profile  Send jen a Yahoo! Message  Reply with Quote
i am not sure but you can research on this, asp.net datagrid object can append resultsets to the one already in the grid,

i think i read it somewhere but lazy to do the research for you

HTH

--------------------
keeping it simple...
Go to Top of Page

vishwa_rao
Starting Member

1 Posts

Posted - 06/29/2005 :  17:53:18  Show Profile  Reply with Quote
Declare @tbl1 table (RowId int identity(1,1), ResourceId varchar(100), ResouceDescription varchar(100))
Declare @tbl2 table (RowId int identity(1,1), ChannelDescription varchar(100), ServiceContact varchar(100))

Declare @tbl3 table (RowId int identity(1,1), ResourceId varchar(100), ResouceDescription varchar(100), ServiceContact varchar(100))

I perform some sql and populate tables tbl1 and tbl2. Now I want to concatenate second and third cols from tbl1 and the third col. from tbl2 into tbl3 as above. Please let me know how to do this?

The problem I am trying to solve is this: In a stored procedure, I perform multiple selects across different tables (more than 2) and then combine them all into one result set and return it. I am sure this is common. Since I am new to stored procedure I thought of solving this by combining two temporary tables as above. If there is a better solution please let me know that also.

Thanks
Vishwa

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37316 Posts

Posted - 06/29/2005 :  17:57:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
Please see my question in the other thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51714

Tara
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000