2

I want to call a stored procedure in a join statement of the Select Query.

For example,

Select * 
from test
left join test1 on GetID(test.id)=test1.id

The idea is to match one to many relationship.

The structure of tables would be

Table: Test

ID Name
1  abc
2  te

Table: Test1

Id TestID Name
1   1      xxx
2   1      yyy
3   1      zzz
4   2      aaa

Stored procedure:

Create procedure GETID
    @id int
as
begin
    select top 1 id 
    from test1 
    where testid = @id
end
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ravi Vyas
  • 137
  • 1
  • 4
  • 19

2 Answers2

2

You can convert the stored procedure into an inline table-valued function or you can put the query inside an OUTER APPLY:

SELECT *
FROM test t
OUTER APPLY(
    SELECT TOP 1 id
    FROM test1
    WHERE testid = t.testid
)x
Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
1

Use a scalar function instead.

CREATE FUNCTION GETID
(
@id int
)
RETURNS int
AS
BEGIN
return (select top 1 id from test1 where testid=@id)
END

Or, review methods in post: Get top 1 row of each group

Use cross apply (or outer apply), which executes once on right side of query. Or, use row_number() over partition to rank the group rows and select based on rank.

declare @test table (id int, name varchar(100))
insert into @test (id, name) values (1, 'abc')
insert into @test (id, name) values (1, 'te')
declare @test1 table (id int, testid int, name varchar(100))
insert into @test1 (id, testid, name) values (1, 1, 'xxx')
insert into @test1 (id, testid, name) values (2, 1, 'yyy')
insert into @test1 (id, testid, name) values (3, 1, 'zzz')
insert into @test1 (id, testid, name) values (4, 2, 'aaa')

Select * from @test t
cross apply (select top 1 * from @test1
          where testid = t.id
          order by id) -- change group order as needed
          as t1
Community
  • 1
  • 1
Shep
  • 638
  • 3
  • 15
  • 1
    Though this is correct, I would suggest using an inline table-valued function instead of a scalar function for performance gain. – Felix Pamittan Dec 16 '15 at 01:27