How to use sql IN in Stored Proc?

Question

I have created a stored proc like this

CREATE PROCEDURE chfselfmanagemonitor.GetPatientList
(IN DoctorID INT, IN PATIDs VARCHAR(255))
BEGIN 
select pb.`PatientBasicId`, concat(concat(pb.`PatientFirstName`, ' '), concat(pb.`PatientLastName`, '')) as PatientName 
from patientchfbasicdata pb
where pb.`PatientBasicId` IN (PATIDs)
ORDER BY pb.`PatientBasicId` asc;
END @@ 
DELIMITER ;

However when I call this like this call call GetPatientList(1002,'1001, 1002, 1004'); I only get the result for 1001. Can someone tell what is wrong?

This is SQL Server specific, but I always point to [Erland Sommarskog's articles](http://www.sommarskog.se/arrays-in-sql.html) when I see this question. It has been asked many times before. — Mr Moose, Feb 12 '14 at 01:12

Jonas T · Answer 1 · 2014-02-11T01:31:44.337

0

It looks like you need to split your string into INT array. You can acheive it by creating a function. Read More here.

edited Feb 11 '14 at 01:31

answered Feb 11 '14 at 01:15

Jonas T

2,989
4
32
43

it gives error stating that the StoredProc takes only 2 params not 4. – Jayku Feb 11 '14 at 01:19
Are you doing anything with DoctorID parameter? – Jonas T Feb 11 '14 at 01:23
Oh i see then. You need to find the way to convert that string into INT array. – Jonas T Feb 11 '14 at 01:26

score 0 · Answer 2 · edited May 23 '17 at 11:49

PATIDs is a string, not a list of ints. You need to split the string first then check fo IN. I would also suggest removing spaces from your PATIDs to make the split easier as there won't be a need to trim (eg GetPatientList(1002,'1001,1002,1004'))

See this post for creating a function that will split your string and then allow you to use the IN clause

https://stackoverflow.com/a/11105413/853295

Or this

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

EDIT - Using Method 2 from the second link would result in something like this:

CREATE PROC dbo.GetPatientList
(
PATIDs varchar(255)
)
AS
BEGIN
SET NOCOUNT ON

CREATE TABLE #TempList
(
    PatientBasicId int
)

DECLARE PATIDs varchar(10), @Pos int

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

IF REPLACE(PATIDs, ',', '') <> ''
BEGIN
    WHILE @Pos > 0
    BEGIN
        SET PATIDs = LTRIM(RTRIM(LEFT(PATIDs, @Pos - 1)))
        IF PATIDs <> ''
        BEGIN
            INSERT INTO #TempList (PatientBasicId) VALUES (CAST(PATIDs AS int)) --Use Appropriate conversion
        END
        SET PATIDs = RIGHT(PATIDs, LEN(PATIDs) - @Pos)
        SET @Pos = CHARINDEX(',', PATIDs, 1)

    END
END 

SELECT pb.`PatientBasicId`, concat(concat(pb.`PatientFirstName`, ' '), concat(pb.`PatientLastName`, '')) as PatientName
FROM    patientchfbasicdata pb
    JOIN 
    #TempList t
    ON pb.`PatientBasicId` = t.PatientBasicId

END
GO

And you can call the stored proc in SQL like

exec GetPatientList '1001,1002,1004'

Thx, I tried it and here is how I could create the SP: DELIMITER \\ DROP PROCEDURE IF EXISTS `GetPatientList`\\ CREATE PROCEDURE `GetPatientList` (IN OrderList VARCHAR(500)) BEGIN SET @s = 'select PatientBasicId, PatientFirstName, PatientLastName from patientchfbasicdata where PatientBasicId IN (' + OrderList + ')'; PREPARE stmt1 FROM @s; EXECUTE stmt1; DEALLOCATE PREPARE stmt1; END\\ DELIMITER ; but I get error when I execute it like this: call GetPatientList('1001,1002,1003'); Error code 1064, SQL state 42000: You have an error in your SQL syntax; syntax to use near '1001' — Jayku, Feb 11 '14 at 04:16

How to use sql IN in Stored Proc?

2 Answers2