SELECT from table WHERE items IN (@variable)

You need to make query like "SELECT * from tbl WHERE items IN ('item1', 'item2', 'item3', 'item4')" and you want to send items to IN clause as variable. It’s nice when, for example, you want to execute Stored procedure or, as in example below, use INSERT ... SELECT construction. Then you need to make a fint with quotes.

Below I write an SQL code of my solution.


@Items – parameter for IN, for example ('item1', 'item2', 'item3', 'item4')

@WhereClause – variable with escaped quotes for correct SQL-query, one quote should be escaped with three ones


I created a varchar(max) variable @sql, set value for it and added to SQL-query

Declare  @Items VARCHAR(MAX)
Declare  @WhereClause VARCHAR(MAX)
SET @WhereClause=REPLACE(@Items,',',''',''')

declare @sql varchar(max)

set @sql = 'SELECT t1.Id, 
  FROM tbl as t1
  WHERE t1.Fullname IN ('''+ @WhereClause + ''') '

INSERT into @tempusertable exec(@sql)


No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment Notes of web-specialist
Since 2009