CODEWITHSUNDEEP
sql-serverstored-procedures
Ravi Vyas
Ravi Vyas

Reputation: 137

How to Execute a Stored Procedure in a Join Statement SQL Server

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

Upvotes: 2

Views: 10102

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

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

Upvotes: 2

Shep
Shep

Reputation: 628

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

Upvotes: 1

Related Questions