how to combine two tables in stored procedure

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 2000 Forums

Transact-SQL (2000)

how to combine two tables in stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-06-28 : 10:41:53

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

22864 Posts

Posted - 2005-06-29 : 00:47:22

Can you give the sample data with the result of how you want to concatenate?

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-06-29 : 01:34:45

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...

vishwa_rao
Starting Member

1 Post

Posted - 2005-06-29 : 17:53:18

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-29 : 17:57:03

Please see my question in the other thread:

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

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources