Как использовать табличный параметр с ключевым словом IN

Question

Редактировать:

Я попытался заменить:

@LocationIDs NVARCHAR(MAX) = null,

с

@LocationIDs LocationIdArray READONLY,

но теперь я получаю сообщение об ошибке:

Необходимо объявить скалярную переменную "@LocationIDs".

--END EDIT--

У меня есть эта хранимая процедура, к которой мне нужно обратиться:

CREATE PROCEDURE [dbo].[spAP_GetTechnician_Door_Unlock]   
    @LocationIDs NVARCHAR(MAX) = NULL,  
    @AlarmDateFrom DATETIME = NULL,  
    @AlarmDateTo DATETIME = NULL,  
    @TechnicianID INT = NULL,
    @LocationId INT = NULL
AS
BEGIN
    IF (@LocationIDs = 'x')  
    BEGIN
        SELECT @LocationIDs = dbo.fn_GetAll_Location_Id()  
    END

    DECLARE @query NVARCHAR(MAX);

    SET @query = 'WITH CTE AS (SELECT ROW_NUMBER() OVER(ORDER BY al.Alarm_Log_ID desc)AS RowNumber,  
 isnull(t.Technician_ID,'''')[Technician_ID], (isnull(t.Last_Name,'''') +'' ''+  isnull(t.Name,''''))  TechnicianName,isnull(t.Emailid,'''') as EmailID,isnull(t.phone,'''') as Phone,dbo.fNAP_DateFormat(al.Alarm_date) as Alarm_date,                  
 Al.Site_ID,s.Name as SiteName,al.point_Address,l.location_Name,l.Location_ID ,shs.StatusData                  
from z_EntityMast_Alarm_Log al                   
left join  z_EntityMast_Technician t on al.Technician_ID=t.Technician_id                  
left join z_EntityMast_Site s on s.Site_ID=al.Site_ID                  
left join z_EntityMast_Location l on s.Location_ID=l.Location_id                  
left join z_EntityMast_Site_Hardware_Status shs on s.site_id=shs.siteid                   
left join z_SysVar_Alarm_Type_00004 at on al.Alarm_Type=at.ID                   
where at.Is_Linkable=1 and al.Alarm_Type !=70'               
if(isnull(@LocationId,0)!=0)            
set @query=@query+' and s.Location_ID ='+convert(varchar(12),@LocationId);            
else            
set @query=@query+' and s.Location_ID in ('+@LocationIDs+')';   
if(isnull(@AlarmDateFrom,0)!=0 and @AlarmDateFrom !='')  
set @query=@query+'and (DATEDIFF(DAY,'''+convert(varchar(30),@AlarmDateFrom)+''', al.Alarm_Date)>=0 and  DATEDIFF(DAY,'''+convert(varchar(30),@AlarmDateTo)+''',al.Alarm_Date)<=0)';                
if(isnull(@TechnicianID,0)!=0)  
set @query=@query+'and t.Technician_ID ='+ convert(varchar(10),@TechnicianID);  
set @query=@query + ')';  
set @query=@query +'select * from CTE ';  

    -- PRINT @query  
    EXEC (@query)  
END

Мне нужно оптимизировать его, и я должен использовать табличные параметры для LocationIds параметр, вместо того, чтобы использовать NVARCHAR(MAX),

Проблема в этой строке кода:

SET @query  = @query + ' and s.Location_ID in ('+@LocationIDs+')';   

Мой вопрос: как заменить эту строку кода и заменить ее табличным параметром таким образом, чтобы конкатенация все еще работала?

Спасибо!

Answer 1

SET @query = @query + ' and s.Location_ID in ('+@LocationIDs+')';
Мой вопрос: как заменить эту строку кода и заменить ее табличным параметром таким образом, чтобы конкатенация все еще работала?

Предположим, ваш LocationIdArray имеет это определение:

create type LocationIdArray as table (LocationId int);

Тогда ваш IN должен выглядеть так:

and s.Location_ID in (select LocationId from @LocationIDs)

Это не будет работать в вашем exec так как @LocationID находится во внешней области по отношению к exec, вы можете передать его в качестве параметра в sp_executesql но лучшее, что вы можете сделать, это переписать ваш динамический запрос на статический, поскольку здесь нет причин использовать динамический код.