Geek asks and answers

How to use the IN statement in SQL Server

How to use the IN statement in SQL Server

Here is the table structure

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

While I ask such a query, I can get the value

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

While I execute the above query, I can’t get the entries related to this table that get an error while executing this error.

DECLARE @s VARCHAR(MAX)

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

Select * FROM Sample WHERE Id IN (@s)
+3
source share
3 answers

You use the wrong way

use the following method

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 the INoperator uses whole collections, not a collection of strings.

+2
source

you should use a function that returns the result (takes 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,','))
+2
source

ADO.NET, , SqlDataRecord.

, SQL Server 2008, Table-Valued

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

+1
source

More articles:


All Articles