SQL - using a variable for an IN clause

Question

I wish to do something like the following:

declare @FrameNumber nvarchar(20)
set @FrameNumber = '(p1, p2)'

select from myTable where c1 in @FrameNumber

What is the correct syntax for this?

(for note: I need to pass the value of @FrameNumber in as a parameter to the stored procedure... so I have to at least use the string "p1, p2")

would prefure and answer that was SQL 7 compatible, but SQL 2005 would be sufficient.

score 9 · Answer 1 · answered Dec 22 '09 at 18:45

9

DECLARE @FrameNumbers TABLE (code NVARCHAR(20) PRIMARY KEY)

INSERT
INTO   @framenumbers
VALUES ('p1')

INSERT
INTO   @framenumbers
VALUES ('p2')

SELECT  *
FROM    mytable
WHERE   c1 IN
        (
        SELECT  code
        FROM    @framenumbers
        )

answered Dec 22 '09 at 18:45

Quassnoi

413,100
91
616
614

I need to pass the value of @FrameNumber in as a parameter to the stored procedure... so I have to use the string "p1, p2" – alumb Dec 22 '09 at 18:49
In `SQL Server 2008`, you can do this: http://msdn.microsoft.com/en-us/library/bb510489.aspx – Quassnoi Dec 22 '09 at 18:53

score 2 · Answer 2 · answered Dec 22 '09 at 18:49

2

CREATE FUNCTION [dbo].[func_ParseStringToTable] (@stringIN varchar(2000)) 
RETURNS @tOUT TABLE(RoomID int) AS
BEGIN
DECLARE @pos int
SET @pos=CHARINDEX(',',@StringIN)
WHILE @pos>0
     BEGIN
     INSERT @tOUT(RoomID) SELECT LEFT(@StringIN,CHARINDEX(',',@StringIN)-1)
     SET @stringIN = SUBSTRING(@StringIN,CHARINDEX(',',@StringIN)+1,LEN(@StringIN))
     SET @pos=CHARINDEX(',',@StringIN)
     END
IF LEN(@StringIN)>0
     BEGIN
     INSERT @tOUT(RoomID) SELECT @StringIN
     END
RETURN 
END

usage...

SELECT * FROM table WHERE id IN (func_ParseStringToTable(@ids))

answered Dec 22 '09 at 18:49

Samuel Neff

73,278
17
138
182

'func_ParseStringToTable' is not a recognized built-in function name. – alumb Dec 22 '09 at 18:56
@alumb, right, it's a custom function. The script above creates it. – Samuel Neff Aug 07 '13 at 04:43

score 1 · Answer 3 · answered Dec 22 '09 at 18:58

You could put load those values into a table variable, or you could use dynamic sql. Here are examples of each:

TABLE VARIABLE

DECLARE @FrameNumbers TABLE (
    Frame NVARCHAR(20)
)

INSERT INTO @FrameNumbers (
    Frame
)
SELECT 'p1'
UNION ALL SELECT 'p2'

option 1:

SELECT * FROM myTable WHERE c1 in (
    SELECT Frame
    FROM @FrameNumbers
)

option 2:

SELECT
    m.*
FROM myTable m
INNER JOIN @FrameNumbers f ON f.Frame = m.c1

All that is fine, but this is my favorite:

DYNAMIC SQL

DECLARE 
@FrameNumber nvarchar(20),
@sql nvarchar(max),
@ParamDef nvarchar(1000)

SET @FrameNumber = '(p1, p2)'

SET @sql = N'SELECT FROM myTable WHERE c1 IN ' + @FrameNumber

EXECUTE dbo.sp_ExecuteSQL @sql

score 1 · Answer 4 · answered Mar 23 '17 at 14:20

I have another solution to do with split function,

DECLARE @FrameNumber NVARCHAR(20)
SET @FrameNumber = 'p1,p2'

SELECT * FROM MyTable WHERE ProductCode IN 
(SELECT Value FROM fn_Split(@FrameNumber, ','))

OutPut:

Split Functions:

CREATE FUNCTION fn_Split (
    @String VARCHAR(8000)
    ,@Delimiter CHAR(1)
    )
RETURNS @temptable TABLE (Value VARCHAR(8000))
AS
BEGIN
    DECLARE @idx INT
    DECLARE @slice VARCHAR(8000)

    SELECT @idx = 1

    IF len(@String) < 1
        OR @String IS NULL
        RETURN

    WHILE @idx != 0
    BEGIN
        SET @idx = charindex(@Delimiter, @String)

        IF @idx != 0
            SET @slice = left(@String, @idx - 1)
        ELSE
            SET @slice = @String

        IF (len(@slice) > 0)
            INSERT INTO @temptable (Value)
            VALUES (@slice)

        SET @String = right(@String, len(@String) - @idx)

        IF len(@String) = 0
            BREAK
    END

    RETURN
END

score 0 · Answer 5 · answered Dec 22 '09 at 18:53

0

what version of SQL Server ?

If you are in 2008 you might be able to use table datatypes. Simplifies these things a lot.

answered Dec 22 '09 at 18:53

no_one

1,852
12
11

score 0 · Answer 6 · answered Dec 22 '09 at 18:54

If you are using Sql Server 2005+ have a look at this

--Split
DECLARE @textXML XML
DECLARE @data NVARCHAR(MAX), 
        @delimiter NVARCHAR(5)

SELECT  @data = 'A,B,C',
        @delimiter = ','

SELECT    @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML)
SELECT  T.split.value('.', 'nvarchar(max)') AS data
FROM    @textXML.nodes('/d') T(split)

You can you that as your in table to select from.

Have a look at XML Support in Microsoft SQL Server 2005

score 0 · Answer 7 · answered Dec 22 '09 at 19:14

final solution:

DECLARE @FrameNumbers TABLE (FrameNumber NVARCHAR(20) PRIMARY KEY)

DECLARE @pos int
SET @pos=CHARINDEX(',',@FrameNumber)
WHILE @pos>0 BEGIN
     INSERT @FrameNumbers SELECT LEFT(@FrameNumber,CHARINDEX(',',@FrameNumber)-1)
     SET @FrameNumber = SUBSTRING(@FrameNumber,CHARINDEX(',',@FrameNumber)+1,LEN(@FrameNumber))
     SET @pos=CHARINDEX(',',@FrameNumber)
END
IF LEN(@FrameNumber)>0 BEGIN
     INSERT @FrameNumbers SELECT @FrameNumber
END

select from myTable where c1 in (select FrameNumber from @FrameNumbers)

thanks Quassnoi and Sam, this solution is just a combination of your solutions.

SQL - using a variable for an IN clause

7 Answers7

TABLE VARIABLE

DYNAMIC SQL

Linked