CODEWITHSUNDEEP
sql-serverstored-procedurestable-valued-parameters
Paco G
Paco G

Reputation: 421

How to use table valued parameter with the IN keyword

Edit:

I tried to replace:

@LocationIDs NVARCHAR(MAX) = null,

with

@LocationIDs LocationIdArray READONLY,

but now I get an error saying:

Must declare the scalar variable "@LocationIDs".

--END EDIT--

I have this stored procedure that I need to address: 

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

I need to optimize it and I have to use table valued parameters for the LocationIds parameter, instead of it using NVARCHAR(MAX).

The problem is in this line of code:

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

My question is: how does one replace that line of code and replace it with a table valued parameter in such a way that the concatenation would still work?

Thanks!

Upvotes: 0

Views: 63

Answers (1)

sepupic
sepupic

Reputation: 8687

SET @query = @query + ' and s.Location_ID in ('+@LocationIDs+')';
My question is: how does one replace that line of code and replace it with a table valued parameter in such a way that the concatenation would still work?

Suppose your LocationIdArray has this definition:

create type LocationIdArray as table (LocationId int);

Then your IN should look like this:

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

This won't work within your exec because @LocationID is in the outer scope respect to exec, you can pass it as a parameter in sp_executesql but the best you can do is to rewrite your dynamic query to static one as there is no reason to use dynamic code here.

Upvotes: 1

Related Questions