Untitled
unknown
sql
a year ago
2.4 kB
3
Indexable
USE [LLAB] GO /****** Object: StoredProcedure [dbo].[sp_Boss_Countrys] Script Date: 1/12/2024 9:15:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Boss_Countrys] ( @UserID int = 0 ) AS BEGIN --declare @UserID int = 2 declare @admin int = 0 select @admin = 1 from users where userid = @userid and Tier = 5 print @admin create Table #tCounrties(country nvarchar(255), CountryID int, ParentID int); if @userID = 0 or @admin = 1 begin with cats1 as( select bc.TemplateCatID CategoryID, lc.countryName Country, CountryID , 0 LEvelID, TemplateStatus from BossTemplateCategories bc inner join BossListCountries lc on lc.id = CountryID where ParentID = 0 union all select c.TemplateCatID, Country, p.CountryID , LevelID +1, p.TemplateStatus from BossTemplateCategories c inner join cats1 p on c.parentID = p.CategoryID ) insert into #tCounrties select distinct Country, CountryID, 0 from cats1 where exists(select * from BossTemplateTasks t where cats1.categoryID = t.categoryID); with cte as ( select c2.categoryName as Country, count(distinct c2.ParentID) p from BossCategories c1 inner join BossCategories c2 on c1.id = c2.parentID where c1.parentID = -1 and exists(select * from BossListProducts p where p.productName = c1.categoryName) group by c2.categoryName ) update t set ParentID = p from cte c inner join #tCounrties t on t.country = c.Country end else insert into #tCounrties select c2.categoryName as Country, lc.id, count(distinct c2.ParentID) from BossCategories c1 inner join BossCategories c2 on c1.id = c2.parentID inner join BossListCountries lc on lc.countryName = c2.categoryName where c1.parentID = -1 and exists(select * from BossListProducts p where p.productName = c1.categoryName) and exists(select * from BossCategoriesLeads where @userID = leadid and catid = c2.id) group by c2.categoryName, lc.id select Country, Parentid Products, isnull(STRING_AGG(u.Userid, ','), '') Users, isnull(STRING_AGG(g.Userid, ','), '') Guests from #tCounrties c1 left join BossCountryAdmins u on u.Countryid = c1.CountryID left join BossCountryGuests G on g.Countryid = c1.CountryID group by Country, Parentid order by Country, Parentid drop Table #tCounrties end GO
Editor is loading...
Leave a Comment