How to use IN Clause with parameter in MS SQL

 

You might be wondering how to pass the value as parameter in IN clause. It is a kind of tricky one. If you directly execute your select query by passing the parameter your query will through error.

Here we will see how it can be done,

First we will create the table

CREATE TABLE [dbo].[EMP](

    [ID] [int] NULL,

    [Name] [nchar](10) NULL

) ON [PRIMARY]

GO

Now we will insert some values to the table

INSERT INTO [Test].[dbo].[T] ([ID],[Name]) VALUES(1,'Mike')

INSERT INTO [Test].[dbo].[EMP] ([ID],[Name]) VALUES(2,'John')

INSERT INTO [Test].[dbo].[EMP] ([ID],[Name]) VALUES(3,'Russel')

INSERT INTO [Test].[dbo].[EMP] ([ID],[Name]) VALUES(4,'Thomas')

INSERT INTO [Test].[dbo].[EMP] ([ID],[Name]) VALUES(5,'David')

GO

Finally we will query the table using IN. I want all the Employee with the ID in 3,4,5 and our query for that should be

select * from t

Declare @id varchar(20)

set @id= '3,4,5'

exec('SELECT * FROM EMP WHERE ID IN('+@id+')')

Above query was useful if you wanted to use INT or any other numeric data type in your IN clause. What you will do if you wanted to use it for char data type?

Yes we will see that as well here.

Query for Char data type should be,

DECLARE @id VARCHAR(MAX)

set @id= 'Mike,John,David'

SELECT @id = '''' + REPLACE(@id,',',''',''') + ''''

EXEC('SELECT * FROM EMP WHERE Name IN('+@id+')')

GO

Result after executing above query for Int data type,

Result after executing above query for CHAR data type,