CREATE OR ALTER PROC [Verified].[Pr_AssociationsByMemberId]
(
	@IDNumberOrBusinessRegistrationNo VARCHAR(50),
	@ProvinceID BIGINT
)
--EXEC Verified.Pr_AssociationsByMemberId '5607170813080'
AS
BEGIN 
	DECLARE @MemberID bigint

	SELECT TOP 1 @MemberID = MemberID from Verified.Members where IDNumberOrBusinessRegistrationNo = @IDNumberOrBusinessRegistrationNo

	IF ISNULL(@MemberID,-1) = -1
	BEGIN
		SELECT A.AssociationID, A.AssociationName, A.RegionID, R.ProvinceID
		FROM Verified.Associations AS A INNER JOIN Md.Regions AS R 
		ON A.RegionID = R.RegionID
		WHERE R.ProvinceID = @ProvinceID
		ORDER BY A.AssociationName
	END
    ELSE
	BEGIN
		SELECT A.AssociationID, A.AssociationName, A.RegionID, R.ProvinceID 
		FROM Verified.Associations A INNER JOIN Verified.AssociationMembers AM
		ON A.AssociationID = AM.AssociationID INNER JOIN Verified.Members M
		ON AM.MemberID = M.MemberID INNER JOIN Md.Regions R 
		ON A.RegionID = R.RegionID 
		WHERE (CASE WHEN ISNULL(@IDNumberOrBusinessRegistrationNo, '') <> '' THEN CASE WHEN M.IDNumberOrBusinessRegistrationNo = @IDNumberOrBusinessRegistrationNo THEN 1 ELSE 0 END ELSE 0 END) = 1
		ORDER BY AssociationName ASC
	END
END
GO

UPDATE [Integration].[DataSources]
SET RequestQuery = 'EXEC Verified.Pr_AssociationsByMemberId @IDNumberOrBusinessRegistrationNo, @ProvinceID' 
WHERE DataSourceName = 'MemberAssociations'
GO