SQL IN() without dynamic SQL

Got a question a while ago how to solve a problem that involved a SQL stored procedure containing the use of ‘IN’ whose data you submitted to the procedure. This was solved as many solve it, with dynamic SQL. This can be likened to building your SQL query from strings in your SP and then executing the query. For example

CREATE PROC foobar @ids as nvarchar(500) as
begin
  declare @sql as varchar(500)
  SET @sql='select foo from bar where foo_id in ('+@ids+')'
 
  ...

‘ids’ here is a comma-separated list of the id’s that we want to do our look up against. The dynamic SQL query is not part of our SP but runs in its own scope. The disadvantage of this approach, however, is that it is not so easy for the query optimizer to be able to figure out how to best run your query. In addition to this, users of our SP must now also have SELECT rights on all tables from which we in our dynamic query retrieve data. You do not need this for an SP as the rights are set to the SP and it then does not matter which tables our SP retrieves data from. Dynamically generating your query can also leave the door open for SQL injections. If you find yourself in a situation where you need to use dynamic SQL, try always executing them with ‘sp_executesql’.

One way to keep the ability to dynamically send data to ‘IN’ but avoid dynamic SQL is to create a function that splits your comma separated list and saves the values ​​in a temporary table which you then use for your look up.

A function for splitting a string can be done in many ways. Below is a variant that makes it possible to choose which character you want to split.

CREATE FUNCTION dbo.Split(@String varchar(8000),
                          @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
 
    select @idx = 1
    if len(@String)<1 or @String is null return
 
    while @idx!= 0
        begin 14 set @idx = charindex(@Delimiter,@String)
        
        if @idx!=0 16 set @slice = left(@String,@idx - 1)
        else 18 set @slice = @String 19 20 if(len(@slice)>0)
        
        insert into @temptable(Items) values(@slice)
        
        set @String = right(@String,len(@String) - @idx)
        
        if len(@String) = 0 break
        
    end
    return
end

Now you can use this feature and split your string containing your id’s.

CREATE PROC foobar @ids as nvarchar(500) as
begin
    declare @sql as varchar(500)
    
    SELECT foo FROM bar WHERE foo_id IN(SELECT CONVERT(int, items) FROM dbo.Split(@ids, ','))
    
...

However, dynamic SQL is not always bad and can sometimes be just the way to go. So test both solutions and see what works best for your particular purpose. But keep an eye on the security details.