Table Valued Parameter to generate LIKE conditions in dynamic query

Question

Post reply

Table Valued Parameter to generate LIKE conditions in dynamic query

buddhatripp

Valued Member

Points: 63
More actions
May 28, 2019 at 2:58 pm

#3647883

It just seems like there has to be a more efficient way to do the generation of the like conditions, parameterized at least:
create type dbo.udt_stringtable as table
(
id int identity(1,1) primary key,
string varchar(4000) not null
);
go
create procedure dbo.usp_InsectMessageLogSearch
(
@EntityId uniqueidentifier,
@AddedFrom datetimeoffset,
@AddedTo datetimeoffset,
@messagesearchterms dbo.udt_stringtable readonly
)
as
begin
declare @EntityIdParameter nvarchar(100) = N'@EntityId uniqueidentifier';
declare @AddedFromParameter nvarchar(100) = N'@AddedFrom uniqueidentifier';
declare @AddedToParameter nvarchar(100) = N'@AddedTo uniqueidentifier';
declare @sql varchar(max);
set @sql = 'SELECT icml.*
FROM
AMP.dbo.InterfaceConnectionMessageLog icml
INNER JOIN AMP.dbo.InterfaceConnection ic on icml.InterfaceConnectionID = ic.InterfaceConnectionID
INNER JOIN AccuMasterSync.dbo.Entity e on ic.EntityID = e.EntityID (NOLOCK)
WHERE e.EntityID = @EntityId
AND icml.Added BETWEEN @AddedFrom AND @AddedTo';
declare @MessageSearchTerm varchar(4000);
declare @id int;
declare @index int = 1;
declare @maxindex int = (select count(id) from @messagesearchterms);
while @index <= @maxindex begin
select
@id = id,
@MessageSearchTerm = string
from @messagesearchterms
where id = @index;
set @sql = @sql + ' AND icml.Message LIKE ''%' + @MessageSearchTerm + '%''';
set @index += 1;
end
exec sp_executesql @sql, @EntityIdParameter, @AddedFromParameter, @AddedToParameter, @EntityId, @AddedFrom, @AddedTo;
end
go

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80356 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jonathan AC Roberts SSCoach Points: 18407 More actions · Answer 2

If you create a temporary table by inserting the contents of table variable @messagesearchterms into it (say call the temporary table #messagesearchterms) you should be able to change the SQL just add this at the end:

AND NOT EXISTS(SELECT *
                 FROM #messagesearchterms m 
                WHERE icml.Message NOT LIKE '%' + m.string + '%')

Jonathan AC Roberts SSCoach Points: 18407 More actions · Answer 3

In fact you don't need any dynamic SQL.

This statement will do everything the dynamic SQL does:

SELECT icml.*
  FROM AMP.dbo.InterfaceConnectionMessageLog icml
 INNER JOIN AMP.dbo.InterfaceConnection ic 
         ON icml.InterfaceConnectionID = ic.InterfaceConnectionID
 INNER JOIN AccuMasterSync.dbo.Entity e WITH (NOLOCK)
         ON ic.EntityID = e.EntityID 
 WHERE e.EntityID = @EntityId
   AND icml.Added BETWEEN @AddedFrom AND @AddedTo
   AND NOT EXISTS(SELECT *
                    FROM @messagesearchterms m 
                   WHERE icml.Message NOT LIKE '%' + m.string + '%')