sql server - dynamic filters and sp_executesql -

i have stored procedure has parameter @somefilterids, takes in comma separated integer ids. if parameter not null translated this:

and [x] in(1, 2, 4)  

and assigned @somefilter

i used along lines:

set @sql = n' ...where      c.somedate >= @somedate ' + @somefilter 

and:

set @parameterdefinition = n'@somedate datetime'; exec sp_executesql  @sql ,@parameterdefinition ,@somedate = @somedate 

i think not best practice , opens potential security holes. correct? can improved? thanks.

i think instead of @somefilterids varchar parameter can use xml type variable , use inner join main table xml variable. avoid dynamic query execution , be safer.

example:

--instead of comma separated id use below xml declare @xml xml = '<row><id>1</id></row><row><id>3</id></row>'  --assume other table declare @yourtable table (itemid int, cola varchar(20))  insert @yourtable select 1, 'hello world'   --joining both tables  select  col.value('data(id[1])', 'int') id    @xml.nodes('/row') tbl(col)  inner join @yourtable t2  on t2. itemid=tbl.col.value('data(id[1])', 'int') --where  c.somedate >= @somedate