CODEWITHSUNDEEP
sql-serverstored-proceduresjoin
ARITHABORT
ARITHABORT

Reputation: 11

SQL Join tables based on Id in Stored Procedure

I need to pull all the policies' cols for a claiment's ID with a JOIN stored Procedure.

pseudocode : select * from policies(tbl) where parties.id = policy.policyNumber.

Here's what I have so far...

CREATE PROCEDURE [dbo].[usp_GetPolicyForClaimentByPolicyIdNumber]
(
    @IdNumber varchar(255) = null
)
AS
BEGIN
SELECT *
    FROM [BinderCurrent].[Policy]
    LEFT JOIN [BinderCurrent].[Policy] ON ([BinderCurrent].[Parties].Id = [BinderCurrent].[PolicyRoles].PolicyId)
    WHERE   [BinderCurrent].[PolicyRoles].PolicyRoleTypeId = 40
    AND (@IdNumber IS NULL OR [BinderCurrent].[Parties].IdNumber LIKE ''+@IdNumber+'%') 
    ORDER BY Id DESC

END

Upvotes: 0

Views: 63

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Filter with ON clause : 

SELECT *
FROM [BinderCurrent].[Policy] LEFT JOIN 
     [BinderCurrent].[Policy] 
     ON ([BinderCurrent].[Parties].Id = [BinderCurrent].[PolicyRoles].PolicyId AND
         [BinderCurrent].[PolicyRoles].PolicyRoleTypeId = 40
WHERE (@IdNumber IS NULL OR [BinderCurrent].[Parties].IdNumber LIKE ''+@IdNumber+'%')
ORDER BY Id DESC;

Note : Table [BinderCurrent].[PolicyRoles] should also appeared with JOINs. This assumes that is part of your query.

Upvotes: 1

Related Questions