How to pass parameter to IN in sql server

Question

Here is my table named Employee

-----------------------
Column Name | Data Type
----------------------
ID          | int
EmpId       | nvarchar
Name        | nvarchar
Salary      | decimal

Have a look all the records of the table

------------------------------
ID | EmpId |  Name   |  Salary
------------------------------
1  | 200   |  Bulbul | 2000.00
2  | 201   |  Ahmed  | 2000.00
3  | 202   |  Rakib  | 2500.00
4  | 203   |  Rubel  | 3000.00
5  | 204   |  Zia    | 4000.00

Now if I want get all the records of a given employee id to the IN operator, I get the following result. It's just fine.

SELECT EmpId, Name, Salary
FROM Employee
WHERE EmpId IN ('200','201')

------------------------------
ID | EmpId |  Name   |  Salary
------------------------------
1  | 200   |  Bulbul | 2000.00
2  | 201   |  Ahmed  | 2000.00

But if I pass the employee id as parameter, then I don't get my desired results. Just get empty result.

DECLARE @Params AS NVARCHAR(MAX) = '''200'',''201'''

SELECT EmpId, Name, Salary
FROM Employee
WHERE EmpId IN (@Params)  

------------------------------
ID | EmpId |  Name   |  Salary
------------------------------
   |       |         |  
   |       |         |

Now I need to get the following result using parameter in IN operator. My desired result is something like:

------------------------------
ID | EmpId |  Name   |  Salary
------------------------------
1  | 200   |  Bulbul | 2000.00
2  | 201   |  Ahmed  | 2000.00

Please help me to get my desire result. Thank in advance.

What version of SQL Server?

Martin Smith
– Martin Smith

2014-08-26 05:31:28 +00:00
Commented Aug 26, 2014 at 5:31 — Martin Smith
– Martin Smith, Commented Aug 26, 2014 at 5:31

mehdi lotfi · Accepted Answer · 2014-08-26 06:51:49Z

4

I suggest that use Table Type Parameter.

CREATE TYPE dbo.IdList AS TABLE (Id INT)
GO

DECLARE @Ids dbo.IdList 

INSERT INTO @Ids(Id)VALUES(200),(201)

SELECT EmpId, Name, Salary
FROM Employee
WHERE EmpId IN (SELECT Id FROM @Ids)

edited Aug 26, 2014 at 6:51

answered Aug 26, 2014 at 5:00

mehdi lotfi

11.6k18 gold badges85 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

TechDo Over a year ago

Getting an error Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'READONLY'.

Lasse V. Karlsen Over a year ago

Drop READONLY and make sure to insert into @Ids and it works just fine. And never, ever, say "getting an error" or the equivalent, always post the exact error message you get.

mehdi lotfi Over a year ago

@Lasse V Karlsen. Tanks For your comment.

mehdi lotfi Over a year ago

@NEO. I correct my answer. It's execute without any error.

Nirav Mehta · Accepted Answer · 2014-08-26 05:05:58Z

Add below Table valued SQL Function fn_split:

CREATE FUNCTION [dbo].[fn_Split](@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
    @value varchar(8000),
    @bcontinue bit,
    @iStrike smallint,
    @iDelimlength tinyint

IF @sDelim = 'Space'
    BEGIN
    SET @sDelim = ' '
    END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
    BEGIN
    WHILE @bcontinue = 1
        BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.

        IF CHARINDEX(@sDelim, @sText)>0
            BEGIN
            SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
                BEGIN
                INSERT @retArray (idx, value)
                VALUES (@idx, @value)
                END

--Trim the element and its delimiter from the front of the string.
            --Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
            SET @idx = @idx + 1
            SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))

            END
        ELSE
            BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
 SET @value = @sText
                BEGIN
                INSERT @retArray (idx, value)
                VALUES (@idx, @value)
                END
            --Exit the WHILE loop.
SET @bcontinue = 0
            END
        END
    END
ELSE
    BEGIN
    WHILE @bcontinue=1
        BEGIN
        --If the delimiter is an empty string, check for remaining text
        --instead of a delimiter. Insert the first character into the
        --retArray table. Trim the character from the front of the string.
--Increment the index and loop.
        IF DATALENGTH(@sText)>1
            BEGIN
            SET @value = SUBSTRING(@sText,1,1)
                BEGIN
                INSERT @retArray (idx, value)
                VALUES (@idx, @value)
                END
            SET @idx = @idx+1
            SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)

            END
        ELSE
            BEGIN
            --One character remains.
            --Insert the character, and exit the WHILE loop.
            INSERT @retArray (idx, value)
            VALUES (@idx, @sText)
            SET @bcontinue = 0  
            END
    END

END

RETURN
END

And execute this query to get your output:

DECLARE @Params AS NVARCHAR(MAX) = '200,201'
SELECT EmployeeId, Name
FROM Employee
WHERE EmployeeId IN (select value from dbo.fn_Split(@Params,','))

Hope this helps!

This solution didn't help? Why you voted down? I've checked it and it is working fine with your expected output in a statement query with optimized way in light weight execution plan?

TechDo · Accepted Answer · 2014-08-26 05:01:04Z

0

Please try:

DECLARE @Params AS NVARCHAR(MAX) = '200,201'

exec ('SELECT EmpId, Name, Salary
       FROM Employee
       WHERE EmpId IN ('+@Params+')')

answered Aug 26, 2014 at 5:01

TechDo

18.7k3 gold badges55 silver badges64 bronze badges

3 Comments

Neeraj Prasad Sharma Over a year ago

If you are sure that sql injection not a problem,. i would say this is best answer. this query will be optimal as can use statistics. but well recompile all the time whenever call. PS. this comment from just performance point of view.

toadflakz Over a year ago

-1 Because dynamic query execution is always slower and to be avoided at all costs.

toadflakz Over a year ago

Yep. Read up on execution plans and SQL injection attacks. Writing code that uses dynamic SQL is not a good idea.

Jakir Hossain · Accepted Answer · 2014-08-30 03:17:23Z

0

Finally I got my expected result doing something like:

CREATE TABLE #EmpTable
(
  EmpId NVARCHAR (50) NULL
)
GO
DECLARE @Parameter AS NVARCHAR(MAX) = '(''201''),(''202'')'
DECLARE @SQL AS NVARCHAR(MAX) ='INSERT INTO #EmpTable (EmpId)VALUES' + @Parameter
EXEC (@SQL)
GO
SELECT EmpId, Name, Salary
FROM Employee
WHERE EmpId IN (SELECT EmpId FROM #EmpTable)

edited Aug 30, 2014 at 3:17

answered Aug 26, 2014 at 6:26

Jakir Hossain

4311 gold badge7 silver badges16 bronze badges

Collectives™ on Stack Overflow

How to pass parameter to IN in sql server

4 Answers 4

4 Comments

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related