asked on

SQL - How do I pass a string of ID's to a SQL statment to get results

I am creating a stored procedure. I will be passing in a string parameter of ID's. How do I write my SQL code to select those ID's.

Error I'm getting is:
Msg 245, Level 16, State 1, Line 9
Conversion failed when converting the varchar value '26395, 26396' to data type int.

Here is the code:

DECLARE @POID AS VARCHAR(200) = '26395, 26396'

SELECT * FROM PO WHERE POID IN (@POID)

Open in new window

Pavel Celba

Several ways exist, e.g.:

1)

SELECT * FROM PO WHERE CHARINDEX(','+CAST(POID AS varchar)+',', ','+REPLACE(@POID, ' ', '')+',') > 0

Open in new window

DECLARE @SQL NVARCHAR(500)
SET @SQL = 'SELECT * FROM PO WHERE POID IN (' + @POID + ')'
EXEC sp_executesql @SQL

Open in new window

CipherIS

ASKER

The first one doesn't work.

The second one does.

But, I need to store the results in a temporary table #Temp

How can I do this?

ste5an

Read Erland's famous article: Arrays and Lists in SQL Server.

Baseline is: Use a table or a table-valued parameter. Otherwise you need to parse it, e.g. by using XML.

CREATE PROCEDURE p_Test @List NVARCHAR(MAX)
AS
SET NOCOUNT ON;

DECLARE @ListAsXml XML = CAST('<ID>' + REPLACE(@List, ',', '</ID><ID>' + '</ID>) AS XML);
DECLARE @IDList TABLE ( ID INT NOT NULL );

INSERT INTO @IDList ( ID )
    SELECT  L.ID.value('.', 'INT')
    FROM    @ListAsXml.nodes('/ID') L ( ID )

SELECT  T.*
FROM    yourTable T
    INNER JOIN @IDList L ON L.ID = T.ID;

Open in new window

(untested).

CipherIS

ASKER

@ste5an

So, the first ID is being added to @IDList but the second ID is being set to ZERO. Only getting one record.

DECLARE @List AS VARCHAR(MAX) = '26395, 26396'

DECLARE @IDList TABLE ( ID INT NOT NULL );
DECLARE @ListAsXml XML = CAST('<ID>' + REPLACE(@List, ',', '</ID><ID>' + '</ID>') AS XML);

INSERT INTO @IDList ( ID )
    SELECT  L.ID.value('.', 'INT')
    FROM    @ListAsXml.nodes('/ID') L ( ID )

SELECT * FROM @IDList

Open in new window

CipherIS

ASKER

@ste5an

I was able to get it to work like this.

DECLARE @List AS VARCHAR(MAX) = '<ID>26395</ID>, <ID>26396</ID>'

DECLARE @IDList TABLE ( ID INT NOT NULL );

DECLARE @ListAsXml XML = @List;

SELECT @ListAsXml

Open in new window

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pavel Celba

You wrote "The first one does not work" but it works for me:

DECLARE @POID VARCHAR(200) = '26395, 26396'
DECLARE @PO TABLE (POID int)
INSERT INTO @PO VALUES (26395), (1), (26396), (26397)

SELECT * FROM @PO WHERE CHARINDEX(','+CAST(POID AS varchar)+',', ','+REPLACE(@POID, ' ', '')+',') > 0

Open in new window

If you need to store results into temp table simply use INTO clause:

SELECT * INTO #temp FROM @PO WHERE CHARINDEX(','+CAST(POID AS varchar)+',', ','+REPLACE(@POID, ' ', '')+',') > 0

Open in new window

Kevin Cross

I think for:

'<ID>' + REPLACE(@List, ',', '</ID><ID>' + '</ID>)

You meant:

'<ID>' + REPLACE(@List, ',', '</ID><ID>') + '</ID>'

ste5an

Ahh. spotted well.