SP returning multiple resultsets from related tables

[gerry]

I have 3 tables A , B & C where B & C are linked to A via FK

TableA
A_ID PK
A_VAL1
A_VAL2

TableB
A_ID FK
B_ID PK
B_VAL1
B_VAL2

TableC
A_ID FK
C_ID PK
C_VAL1
C_VAL2


I want to create a stored procedure that takes a string
and uses it to search for matches in B_VAL1 & C_VAL1 giving a list of A_ID
values
and returns 3 resultsets consisting of the rows matching the selected A_ID
values from each table.

Here is what I came up with :

DECLARE @IDs TABLE { ID int )

INSERT INTO @IDs
SELECT [A_ID] from [TableB] WHERE [B_VAL1] LIKE @SearchStr
UNION
SELECT [A_ID] from [TableC] WHERE [C_VAL1] LIKE @SearchStr

SELECT * FROM [TableA] INNER JOIN @IDs ON [TableA].[ID] = [@IDs].[ID]
SELECT * FROM [TableB] INNER JOIN @IDs ON [TableB].[A_ID] = [@IDs].[ID]
SELECT * FROM [TableC] INNER JOIN @IDs ON [TableC].[A_ID] = [@IDs].[ID]


This seems to work well, but I was wondering if there is a better way to do
this ?

Gerry

 

[Cor Ligthert[MVP]]

Depends completely where you use this.

As it is for Crystal Reports, then it is probably the most simple way

Cor