CODEWITHSUNDEEP
sqlsql-server
user1307481
user1307481

Reputation:

How to use IN Operator in SQL Server

How to use IN Operator in SQL Server

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)

Upvotes: 3

Views: 12687

Answers (3)

Michael Buen
Michael Buen

Reputation: 39393

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

Upvotes: 1

Royi Namir
Royi Namir

Reputation: 148524

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,','))

Upvotes: 2

Gaurav Agrawal
Gaurav Agrawal

Reputation: 4431

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..

Upvotes: 2

Related Questions