Passing a string of IDs to a WHERE IN clause

Scenario: There is a stored procedure that accepts a comma separated string of Ids as ‘1589, 1586, 1587’.


Stored procedure:

CREATE PROCEDURE [dbo].[Employee_GetByIds]
@IDs VARCHAR(1000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT Id,FirstName, LastName, Address, Phone FROM Employee WHERE Id IN (@IDs)
END


Issue: application threw an error since the Id field of the Employee table is in the type of int but the parameter (@IDs) is with the type of varchar. From our application where pass the string of ID list will be something like ‘1585,1586,1587’. So we have to hold this value in a string type variable and pass the value to the stored procedure. Stored procedure accepts the value as a varchar type parameter but it uses it in a int type field. That’s where the problem arises.

Solution: Use a table-valued function to insert the list of IDs to a table and use the function within the stored procedure to select the list of IDs from that table instead of comparing the raw ID list.


Splitter function that inserts the list of IDs to a table:

ALTER FUNCTION [dbo].[Splitter] (@IDs VARCHAR(100) )
RETURNS @Tbl_IDs TABLE(ID INT) AS

BEGIN
-- Append comma

SET @IDs = @IDs + ','
-- Indexes to keep the position of searching

DECLARE @Pos1 INT
DECLARE @pos2 INT

-- Start from first character

SET @Pos1=1
SET @Pos2=1

WHILE @Pos1 < LEN(@IDs)
BEGIN
SET @Pos1 = CHARINDEX(',',@IDs,@Pos1)
INSERT @Tbl_IDs SELECT CASE(SUBSTRING(@IDs,@Pos2,@Pos1-@Pos2) AS INT)
-- Go to next non comma character

SET @Pos2=@Pos1+1
-- Search from the next charcater

SET @Pos1 = @Pos1+1
END
RETURN
END


Stored procedure has been altered to use the list of IDs converted to a table value by the splitter function:

ALTER PROCEDURE [dbo].[SourceDocument_GetByIDs]
@IDs VARCHAR(1000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Id,FirstName, LastName, Address, Phone FROM Employee WHERE Id IN (SELECT ID FROM Splitter(@IDs))
END