How to use IN Operator in SQL Server

Question

Here Is the table Structure

Create Table Sample(Id INT,Name Varchar(50))

While I am the Query like this I can get the Value

Select * FROM Sample WHERE Id IN ('74','77','79','80')

While I am executing the above Query I can't able to get the Records Related to that table getting error executing this error.

DECLARE @s VARCHAR(MAX)

SET @s='74','77','79','80'

Select * FROM Sample WHERE Id IN (@s)

This may help stackoverflow.com/questions/2197736/in-operator-sql — Caffeinated
– Caffeinated, Commented Apr 20, 2012 at 5:59

Gaurav Agrawal · Accepted Answer · 2012-04-20 05:58:43Z

2

You are using wrong way

use the following way

DECLARE @s VARCHAR(MAX)
DECLARE @d VARCHAR(MAX)

SET @s='74 , 77 , 79 , 80'

set @d = 'select * from arinvoice where arinvoiceid in('+@s+')'
exec (@d)

here IN operator use integers collection not string collection..

answered Apr 20, 2012 at 5:58

Gaurav Agrawal

4,44110 gold badges47 silver badges64 bronze badges

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

Comments

Royi Namir · Accepted Answer · 2012-04-20 05:59:34Z

2

you should use a function which gives back a result set ( takes a csv format and returns a table)

SET ANSI_NULLS ON 
SET QUOTED_IDENTIFIER ON 

GO 

ALTER FUNCTION [dbo].[Splitt] (@String    NVARCHAR(4000), 
                               @Delimiter CHAR(1)) 
RETURNS @Results TABLE ( 
  Items NVARCHAR(4000)) 
AS 
  BEGIN 
      DECLARE @Index INT 
      DECLARE @Slice NVARCHAR(4000) 

      SELECT @Index = 1 

      IF @String IS NULL 
        RETURN 

      WHILE @Index != 0 
        BEGIN 
            SELECT @Index = Charindex(@Delimiter, @String) 

            IF @Index <> 0 
              SELECT @Slice = LEFT(@String, @Index - 1) 
            ELSE 
              SELECT @Slice = @String 

            IF ( NOT EXISTS (SELECT * 
                             FROM   @Results 
                             WHERE  items = @Slice) ) 
              INSERT INTO @Results 
                          (Items) 
              VALUES      (@Slice) 

            SELECT @String = RIGHT(@String, Len(@String) - @Index) 

            IF Len(@String) = 0 
              BREAK 
        END 

      RETURN 
  END

and now you can write :

DECLARE @s VARCHAR(MAX)

SET @s='74,77,79,80'

Select * FROM Sample WHERE Id IN (select items from dbo.Splitt(@s,','))

answered Apr 20, 2012 at 5:59

Royi Namir

149k145 gold badges502 silver badges833 bronze badges

2 Comments

Royi Namir Over a year ago

it can be tuned up basically, but it is the right way. there are few situations which we should make "string sql execs". this is not one of them

Royi Namir Over a year ago

@user1307481 is it slow or not fast enough? these are 2 different things... you have accepted the wrong answer(not saying youve should accept myne).

Michael Buen · Accepted Answer · 2012-04-20 06:15:56Z

1

If you are using ADO.NET, you can avoid the magic string, just use SqlDataRecord.

Or if you are using SQL Server 2008, you can also avoid the magic string by using Table-Valued Parameter

Source: http://www.sommarskog.se/arrays-in-sql-2008.html

answered Apr 20, 2012 at 6:15

Michael Buen

39.6k10 gold badges99 silver badges120 bronze badges

Collectives™ on Stack Overflow

How to use IN Operator in SQL Server

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related