How to use a variable to delete with like? SQL Server

Question

I’m having trouble developing the T-SQL below:

DECLARE @NOME_FILIAL VARCHAR(25); DECLARE @DATA_AJUSTE DATETIME;

SET @NOME_FILIAL = (SELECT FILIAL FROM FILIAIS WHERE COD_FILIAL='125518');

SET @DATA_AJUSTE = (SELECT DATA_AJUSTE FROM ESTOQUE_PRODUTOS  WHERE FILIAL = @NOME_FILIAL AND DATA_AJUSTE IS NOT NULL GROUP BY DATA_AJUSTE)

UPDATE ESTOQUE_PRODUTOS SET DATA_AJUSTE = '20190101' WHERE FILIAL = @NOME_FILIAL AND DATA_AJUSTE IS NOT NULL

GO

DELETE FROM LJ_ETL_REPOSITORIO WHERE ID_ETL_TIPO='171' AND FILTRO LIKE %@NOME_FILIAL%

I can’t use the variable with like:

DELETE FROM LJ_ETL_REPOSITORIO WHERE ID_ETL_TIPO='171'
AND FILTRO LIKE %@NOME_FILIAL%

Answer 1

Try to do so:

DELETE FROM LJ_ETL_REPOSITORIO WHERE ID_ETL_TIPO='171' AND FILTRO LIKE '%' + @NOME_FILIAL + '%'

Answer 2

You can use a dynamic query, as the example below

EXEC(N'DELETE FROM LJ_ETL_REPOSITORIO WHERE ID_ETL_TIPO=''171'' AND FILTRO LIKE ''%' + @NOME_FILIAL + '%''')