Set sql query with LIKE operator in mssql

Question

I am getting error on % operator while executing. Can you please help me to write proper way.

SET @sql = N'DELETE TOP(1000) FROM ' + @tableName + ' WHERE ItemId
        in (Select ItemId from dbo.History_Item 
            WHERE (
                [FullyQualifiedName] LIKE ''' + @machine_DS + '.%'''
                OR 
                [FullyQualifiedName] Like '''+ @machine_Kpi + '.%'''
                )
            )
        AND datetime between'''+ @startDate +''' And '''+ @endDate +'''

I followed this link How to parameterize query with like operator in Sql Server

But unable set it perfectly. I know it is duplicate query but I am facing trouble to set it.

Answer 1

It should be something like this:

NB do you really want to use .% in your like operator?

SQL Code

declare @sql nvarchar(max)
declare @tablename nvarchar(max) = 'test'
declare @machine_ds nvarchar(max) ='testmachine'
declare @startdate nvarchar(max) = '2018-06-25'
declare @enddate nvarchar(max) = '2018-06-26'
declare @machine_Kpi nvarchar(max) ='testmachinekpi'

SET @sql = 'DELETE TOP(1000) FROM ' + @tableName + ' WHERE ItemId

 in (Select ItemId from dbo.History_Item 
            WHERE (
                [FullyQualifiedName] LIKE ''' + @machine_DS + '.%''
                OR 
                [FullyQualifiedName] Like '''+ @machine_Kpi + '.%''
                )
            )
        AND datetime between'''+ @startDate +''' And '''+ @endDate +''''

       print @sql

SQL Code without injection

declare @sql nvarchar(max)
declare @tablename nvarchar(max) = 'test'
declare @machine_ds nvarchar(max) ='testmachine'
declare @startdate date = '2018-06-25'
declare @enddate date = '2018-06-26'
declare @machine_Kpi nvarchar(max) ='testmachinekpi'

SET @sql = 'DELETE TOP(1000) FROM ' + @tableName + ' WHERE ItemId

 in (Select ItemId from dbo.History_Item 
            WHERE (
                [FullyQualifiedName] LIKE ''' + @machine_DS + '.%''
                OR 
                [FullyQualifiedName] Like '''+ @machine_Kpi + '.%''
                )
            )
        AND datetime between @StartDate And @EndDate'

        exec sp_executesql @SQL,N'@StartDate date, @EndDate date',@StartDate = @startdate,@EndDate = @enddate

SQL Output

DELETE TOP(1000) FROM test WHERE ItemId

 in (Select ItemId from dbo.History_Item 
            WHERE (
                [FullyQualifiedName] LIKE 'testmachine.%'
                OR 
                [FullyQualifiedName] Like 'testmachinekpi.%'
                )
            )
        AND datetime between'2018-06-25' And '2018-06-26'

Answer 2

Just edited your sql script

SET @sql = N'DELETE TOP(1000) FROM ' + @tableName + ' WHERE ItemId
        in (Select ItemId from dbo.History_Item 
            WHERE (
                [FullyQualifiedName] LIKE ''' + @machine_DS + '.%''
                OR 
                [FullyQualifiedName] Like '''+ @machine_Kpi + '.%''
                )
            )
        AND datetime between'''+ @startDate +''' And '''+ @endDate +''''