I need a SP to return multiple sets of results. The second set of results would be based on a column of the first set of results.
So:
declare @myTable1 table(field0 int,field1 varchar(255))
insert into @myTable1 select top 1 field0, field1 from table1
declare @myTable2 table(field0 int,field3 varchar(255))
insert into @myTable2
select field0, field3 from table2
where @myTable1.field0 = @myTable2.field0
How do return @myTable1 and @myTable2 with my SP? Is this syntax even right at all?
My apologies, I'm still a newbie at SQL...
EDIT:
So, I'm getting an error on the last line of the code below that says: "Must declare the scalar variable "@myTable1""
declare @myTable1 table(field0 int,field1 dateTime)
insert into @myTable1
select top 1 field0, field1
from someTable1 m
where m.field4 > 6/29/2009
select * from @myTable1
select *
from someTable2 m2
where m2.field0 = @myTable1.field0
If I highlight and run the code up until the second select * it works fine...
when I highlight the rest it acts like the first variable doesn't exist...
EDIT2: Figured that problem out. Thanks guys.
declare @myTable1 table(field0 int,field1 dateTime)
insert into @myTable1
select top 1 field0, field1
from someTable1 m
where m.field4 > 6/29/2009
select * from @myTable1
select *
from someTable2 m2
where m2.field0 = (select field0 from @myTable1)