Style of using @variable and IN operator

Style of Using @variable and IN Operator

Using Of @variable and IN Operator in single statment

create table test (companyid int, title varchar(100), description varchar(100), keywords varchar(100))

insert into test values(1,'company a','company a','aa')

insert into test values(1,'company b','company b','bb')

insert into test values(1,'company c','company c','cc')

insert into test values(1,'company d','company d','dd')



declare @keywords varchar(100)

set @keywords = 'aa,bb,cc,dd';



WITH CTE AS (

SELECT

CAST('' + REPLACE(@keywords, ',', '') + '' AS XML) AS Keywords

)



select * from test where keywords in (

SELECT RTRIM(LTRIM(ExtractedCompanyCode.X.value('.', 'VARCHAR(256)'))) AS Keywords

FROM CTE

CROSS APPLY Keywords.nodes('//I') ExtractedCompanyCode(X)

)