How to do an in with a comma delimited list

1

I am trying to do the following

SELECT participantid,placementid, NameOfProgram, LastName, FirstName FROM #p WHERE 1 = 1 AND lastname like @lastname AND NameofProgram in (@nameofprogram)

but this is wrong syntax
what's the right way to do this ? the @nameofprogram is a comma delimited list of programs and I want to pull up any that math this

2

Try this:

with cte(prog,rest)
  as (select left(@nameofprogram+',',charindex(',',@nameofprogram)-1)
            ,right(@nameofprogram+',',len(@nameofprogram)-charindex(',',@nameofprogram)+1)
      union all
      select left(rest,charindex(',',rest)-1)
            ,right(rest,len(rest)-charindex(',',rest))
        from cte
       where rest<>''
     )
select p.participantid
      ,p.placementid
      ,p.nameofprogram
      ,p.lastname
      ,p.firstname
  from #p as p
       inner join cte as c
               on c.prog=p.nameofprogram
 where 1=1
   and p.lastname like @lastname
;
3

thaks the problem is this is part of a long dynamic query so I just need to add it
so now i did

IF @NameofProgram IS NOT NULL
SELECT @sql += N' AND NameofProgram in (@nameofprogram)'

How can I keep this dynamic

4

You should (if possible) avoid dynamic queries. Since this is already in a dynamic query, I wonder if the @nameofprogram is build dynamically from another query. If so, it probably the query that extracts the programname might be build into the current query and thus avoiding dynamic.

If you want to stick to dynamic, try this:

SELECT @sql += N' AND NameofProgram in (' + @nameofprogram + N')'
1 Like
5

DON'T DO THAT!

Instead, split the @nameofprogram variable into a keyed table, then INNER JOIN to the table. That will be vastly more efficient.

6

thanks
why is dynamic query not good
in my case I am running a search so the user fills in many different fields to match the table and sometimes 1 can be filled in and sometimes all - so I am building a dynamic query based on what was filled in

what would be the better way to do this?

7

Something like the code below. You can Google the source for "DelimitedSplit8K":

DECLARE @nameofprogram varchar(8000)
IF OBJECT_ID('tempdb.dbo.#nameofprogram') IS NOT NULL
    DROP TABLE #nameofprogram
CREATE TABLE #nameofprogram ( nameofprogram varchar(200) primary key )

INSERT INTO #nameofprogram ( nameofprogram )
SELECT DISTINCT ds.item
FROM dbo.DelimitedSplit8K(@nameofprogram, ',') ds

SELECT p.participantid,p.placementid,p.NameOfProgram,p.LastName,p.FirstName 
FROM #p p
INNER JOIN #nameofprogram np ON np.nameofprogram = p.NameOfProgram
WHERE 1 = 1 AND p.lastname like @lastname
8

thanks - the reason i'm using dynamic sql is that I have about 25 different options and then I can add them on
what is the way oto get around using dynamic sql in this case?

9

You can still use dynamic SQL. You just use temp tables as part of that instead of variables.

10

thanks and how is this better then not using a temporary table?

11

Because it makes SQL Injection impossible for that part of the dynamic SQL.