CODEWITHSUNDEEP
sqlsql-server
Josh Cooper
Josh Cooper

Reputation: 191

SQL Pass multiple IDs as one variable

I'm looking to pass a theoretically unlimited number of IDs to a stored procedure as one variable and have it return all matching records. When using a list and IN statement I'm getting an error as ProviderID datatype is INT and it's treating my list as strings.

DECLARE @ProviderList varchar(max)
SET @ProviderList = '1001,1002' 

SELECT *
FROM   tblProviders
WHERE  ProviderID IN ( @ProviderList )

--Error: Error converting data type varchar to bigint. when used

Separating the IDs like below works...but as there's no way to know how many IDs will be searched at once it would be messy to have potential placeholder variables which aren't required.

DECLARE @ProviderOne varchar(max)
SET @ProviderOne = '1001'

DECLARE @ProviderTwo varchar(max)
SET @ProviderTwo = '1002'

SELECT *
FROM   tblProviders
WHERE  ProviderID IN ( @ProviderOne, @ProviderTwo )

Upvotes: 0

Views: 9792

Answers (5)

Siva Bhupathiraju
Siva Bhupathiraju

Reputation: 1

DECLARE @ProviderList varchar(max)

Declare @sqlstring nvarchar(2000)

SET @ProviderList = '100,200,300' 

set @sqlstring = 'SELECT *  FROM temptable where Num_Key in ('  + @ProviderList + ')'

--select @sqlstring

execute sp_executesql @sqlstring

Upvotes: 0

mauridb
mauridb

Reputation: 1569

If you're using SQL Server 2016 or above, or Azure SQL you can just use JSON:

DECLARE @ProviderList varchar(max)
SET @ProviderList = '[1001,1002]' 

SELECT *
FROM   tblProviders
WHERE  ProviderID IN ( SELECT CAST(p.[Value] AS INT) FROM OPENJSON(@ProviderList) p )

It is the fastest and most secure method available

Upvotes: 0

Thom A
Thom A

Reputation: 95588

A couple ways to do this. One is to do a delimited string and then splitting it out. As I'm not sure what version of SQL Server you're on, you can find a copy of Jeff Moden's Delimited String splitter here.

Then you can do something like:

SELECT *
FROM MyTable MT
WHERE MT.MyValues IN (SELECT Item FROM dbo.delimitedSplit8k(@DelimString,',');

The alternative is to use a custom table-value type. This is where you can create a datatype that is in the form of a table. So, for example:

CREATE TYPE IDs AS TABLE (ID int);
GO

DECLARE @ID IDs;
INSERT INTO @ID
VALUES (1),(2),(3)

SELECT *
FROM MyTable MT
WHERE MT.MyValues IN (SELECT ID FROM @ID);
GO
--Clean up
--DROP TYPE IDs;

Any questions, please comment.

Upvotes: 2

santosh singh
santosh singh

Reputation: 28652

You can accept input as csv and then you can convert csv to list and pass as in parameter

SELECT *
FROM   tblProviders
WHERE  ProviderID IN  (SELECT * FROM dbo.CSVToLIst(@CSV))

CSV to list function

CREATE FUNCTION dbo.CSVToList (@CSV varchar(3000)) 
    RETURNS @Result TABLE (Value varchar(30))
AS   
BEGIN
    DECLARE @List TABLE
    (
        Value varchar(30)
    )

    DECLARE
        @Value varchar(30),
        @Pos int

    SET @CSV = LTRIM(RTRIM(@CSV))+ ','
    SET @Pos = CHARINDEX(',', @CSV, 1)

    IF REPLACE(@CSV, ',', '') <> ''
    BEGIN
        WHILE @Pos > 0
        BEGIN
            SET @Value = LTRIM(RTRIM(LEFT(@CSV, @Pos - 1)))

            IF @Value <> ''
                INSERT INTO @List (Value) VALUES (@Value) 

            SET @CSV = RIGHT(@CSV, LEN(@CSV) - @Pos)
            SET @Pos = CHARINDEX(',', @CSV, 1)
        END
    END     

    INSERT @Result
    SELECT
        Value
    FROM
        @List

    RETURN
END

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

The best way may be to munge the query string and use dynamic SQL. This allows the optimizer to compile the query for optimal execution. This approach is to insert the list of ids directly into the string. Of course, this can be a really bad idea if the list comes from user input, because this approach introduces the risk of SQL injection.

An alternative is to split the string. The most recent versions of SQL Server (2016+) have string_split() (or you can find a UDF on the web quite easily):

SELECT p.*
FROM tblProviders p
WHERE p.ProviderID IN (SELECT string_split(@ProviderList, ',') )

Upvotes: 2

Related Questions