如何将表值参数与 IN 关键字一起使用

Question

编辑：

我试图替换：

@LocationIDs NVARCHAR(MAX) = null,

跟

@LocationIDs LocationIdArray READONLY,

但现在我收到一个错误说：

必须声明标量变量"@LocationIDs"。

-

-结束编辑--

我有这个存储过程，我需要解决：

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但您能做的最好的事情是将动态查询重写为静态查询，因为没有理由在这里使用动态代码。