Combining the results of several queries into one table. 1

[DomTrix]

I have a problem using freetexttable and my only foreseeable solution is this:

I have Stored Procedure 1 (SP1) which calls SP2, SP3 and SP4.

SP2, SP3 and SP4 are all select queries and so return a record set (tho Im not sure where, which is my first question really).

In SP1, after calling the other SPs, I want to collate all three record sets into 1 record set WHEN a particular record appears in ALL 3 record sets.

If this can be achieved and someone can show me how, I will love them long time

<icon with man banging head on wall>
DT

 

[jbenson001]

How are the record sets stored in your procedure.. temp tables, table objects?

If you can access all reslut sets from your calling proc, do and inner join to each table(on what ever your key is), this way only the rows that exist in each result set will return in that select.

 

[NoCoolHandle]

If you are female then ok. otherwise I will take like

go
create proc Return1
as
select lastname from northwind.dbo.employees where employeeid = 1
go
create proc Return2
as
select lastname from northwind.dbo.employees where employeeid = 2
go
Create PRoc Combine1and2
as
Create table #names (lastname varchar(300))
insert into #Names(lastname)
exec return1
insert into #names(lastname)
exec return2
Select * from #names
drop table #names
go
exec Combine1and2
go
Drop proc return1
drop proc return2
drop proc Combine1and2

 

[NoCoolHandle]

Incase it wasn't obvious look at the code in red.

( and a star is enough of your love

go
create proc Return1
as
select lastname from northwind.dbo.employees where employeeid = 1
go
create proc Return2
as
select lastname from northwind.dbo.employees where employeeid = 2
go
[red]
Create PRoc Combine1and2
as
Create table #names (lastname varchar(300))
insert into #Names(lastname)
exec return1
insert into #names(lastname)
exec return2
Select * from #names
drop table #names
[/red]
go
exec Combine1and2
go
Drop proc return1
drop proc return2
drop proc Combine1and2

 

[DomTrix]

Aha, create table. Thatd be it ty.

Thank you Benson, this procedure is theoretical. I was merely putting it forward as a logical solution, I do not know how to implement it which is my problem.

If I dont use create table to store the results of the nested stored procs, can I use variables of the table type?

I have been trying to declare these variables but been getting errors

DECLARE @myRecordSet   table

 

[DomTrix]

Scratch that, your post has shown me the light Send me you address and Ill give you my love

ty ty ty

 

[jbenson001]

with your syntax, you need to actually create the columns..

From Books On Line:

Syntax


Note Use DECLARE @local_variable to declare variables of type table.


table_type_definition ::=
TABLE ( { column_definition | table_constraint } [ ,...n ] )

column_definition ::=
column_name scalar_data_type
[ COLLATE collation_definition ]
[ [ DEFAULT constant_expression ] | IDENTITY [ ( seed , increment ) ] ]
[ ROWGUIDCOL ]
[ column_constraint ] [ ...n ]

column_constraint ::=
{ [ NULL | NOT NULL ]
| [ PRIMARY KEY | UNIQUE ]
| CHECK ( logical_expression )
}

table_constraint ::=
{ { PRIMARY KEY | UNIQUE } ( column_name [ ,...n ] )
| CHECK ( search_condition )
}

Maybe just re-think your logic. Is it necessary to split up your code into 4 spearate SPs, can you do it in one?

Jim

 

[DomTrix]

Yes, it is definately neccessary, I need to combine the results of four FREETEXTTABLE queries but return all rows for each individual query when the search param = "". Passing "" to FREETEXTTABLE causes an undesired error.

But thanks for the help. I had seen that definition but the way they put it was as clear as mud if not used to it. Got it working now thankyou.

 

[jbenson001]

Cool glad I could help

Jim