GetShopsListByCompanies.sql

set noexec off
go
if object_id('GetShopsListByCompanies') is not null
begin
	set noexec on
end	
go
-- создаем пустой объект
-- необходимо указать название процедуры
create procedure dbo.GetShopsListByCompanies as
raiserror ('Объект пустой. Ошибка при создаении объекта', -- Message text.
				16, -- Severity.
				1 -- State.
			);
go
set noexec off
go
alter procedure dbo.GetShopsListByCompanies
(
	@CompaniesList varchar(max)
)
as 
begin

--declare @CompaniesList as varchar(max)

--set @CompaniesList = '1, 2'	
	
	declare @CompaniesTable table (CompanyID int index IX_CompaniesTable_CompanyID nonclustered (CompanyID))
	
	if isnull(@CompaniesList,'') <> ''
	begin
		insert into @CompaniesTable
			select
				Convert(int,Value)
			from STRING_SPLIT(@CompaniesList, ',')
	end

	select 
		s.Id,
		
		case 
			when isnull(s.Name,'') = '' 
				 then '(ShopId = ' + convert(nvarchar(16), s.Id) + ')' 
			 else s.Name 
		end as Name

	from dbo.Shops s with (nolock)
		inner join @CompaniesTable ct on ct.CompanyID = s.CompanyId
		inner join dbo.Terminals t with (nolock) on 
				t.ShopId = s.Id
			and isnull(t.HostName, '') <> ''

	
end