You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
47 lines
1.7 KiB
47 lines
1.7 KiB
CREATE OR ALTER PROCEDURE [dbo].[Organization_UnassignedToProviderSearch] |
|
@Name NVARCHAR(50), |
|
@OwnerEmail NVARCHAR(256), |
|
@Skip INT = 0, |
|
@Take INT = 25 |
|
WITH RECOMPILE |
|
AS |
|
BEGIN |
|
SET NOCOUNT ON |
|
DECLARE @NameLikeSearch NVARCHAR(55) = '%' + @Name + '%' |
|
DECLARE @OwnerLikeSearch NVARCHAR(55) = @OwnerEmail + '%' |
|
|
|
IF @OwnerEmail IS NOT NULL |
|
BEGIN |
|
SELECT |
|
O.* |
|
FROM |
|
[dbo].[OrganizationView] O |
|
INNER JOIN |
|
[dbo].[OrganizationUser] OU ON O.[Id] = OU.[OrganizationId] |
|
INNER JOIN |
|
[dbo].[User] U ON U.[Id] = OU.[UserId] |
|
WHERE |
|
((O.[PlanType] >= 2 AND O.[PlanType] <= 5) OR (O.[PlanType] >= 8 AND O.[PlanType] <= 20) AND (O.PlanType <> 16)) -- All 'Teams' and 'Enterprise' organizations |
|
AND NOT EXISTS (SELECT * FROM [dbo].[ProviderOrganizationView] PO WHERE PO.[OrganizationId] = O.[Id]) |
|
AND (@Name IS NULL OR O.[Name] LIKE @NameLikeSearch) |
|
AND (U.[Email] LIKE @OwnerLikeSearch) |
|
ORDER BY O.[CreationDate] DESC |
|
OFFSET @Skip ROWS |
|
FETCH NEXT @Take ROWS ONLY |
|
END |
|
ELSE |
|
BEGIN |
|
SELECT |
|
O.* |
|
FROM |
|
[dbo].[OrganizationView] O |
|
WHERE |
|
((O.[PlanType] >= 2 AND O.[PlanType] <= 5) OR (O.[PlanType] >= 8 AND O.[PlanType] <= 20) AND (O.PlanType <> 16)) -- All 'Teams' and 'Enterprise' organizations |
|
AND NOT EXISTS (SELECT * FROM [dbo].[ProviderOrganizationView] PO WHERE PO.[OrganizationId] = O.[Id]) |
|
AND (@Name IS NULL OR O.[Name] LIKE @NameLikeSearch) |
|
ORDER BY O.[CreationDate] DESC |
|
OFFSET @Skip ROWS |
|
FETCH NEXT @Take ROWS ONLY |
|
END |
|
END |
|
GO
|
|
|