Bom dia,
se o o banco for 2016 ou posterior, você pode fazer da sequinte forma
DECLARE @CODIGO VARCHAR(MAX);
SET @CODIGO = 'VALOR3;VALOR4';
SELECT VALUE FROM string_split(@CODIGO, ';')
DECLARE @TABELA TABLE
(
DESCRICAO VARCHAR(MAX),
NOME VARCHAR(MAX)
)
INSERT INTO @TABELA VALUES('TESTE 1', 'VALOR1')
INSERT INTO @TABELA VALUES('TESTE 2','VALOR2')
INSERT INTO @TABELA VALUES('TESTE 3', 'VALOR3')
INSERT INTO @TABELA VALUES('TESTE 4', 'VALOR4')
INSERT INTO @TABELA VALUES('TESTE 5','VALOR5')
INSERT INTO @TABELA VALUES('TESTE 6', 'VALOR6')
SELECT * FROM @TABELA WHERE NOME IN (SELECT VALUE FROM STRING_SPLIT(@CODIGO, ';'))
PS: para que a função STRING_SPLIT funcione A compatibilidade do banco tem que ser de 130 ou acima;
Essa configuração fica em propriedades do banco de dados, opções logo abaixo da opção do modelo de recuperação