SQL - Converting a varchar parameter to a table

Introduction

While working on a SmartClient application we had an issue where the client would pass a variable that contained a list of ID numbers to the web service, which would then be forwarded to a stored procedure in SQL Server in order to return a dataset back to the client. The issue we had was that the stored procedure had to use that list in an IN statement. This was achieved by creating a function that accepts the varchar list of numbers and returns a table.

Create the Function

CREATE FUNCTION [dbo].[fn_GetTableFromIntList]
(@strIntList VARCHAR(MAX),
 @strDelimiter VARCHAR(10)
)
 
RETURNS @tblList TABLE (IntValue INT NOT NULL)
 
AS
 
BEGIN
 
DECLARE    @iStartPos INT,@iEndPos INT,@strValue VARCHAR(15)
SET @iStartPos = 1
SET @strIntList = @strIntList + @strDelimiter
SET @iEndPos = CHARINDEX(@strDelimiter, @strIntList)
 
WHILE @iEndPos > 0
 
BEGIN
 
    SET @strValue = SUBSTRING(@strIntList, @iStartPos, @iEndPos - @iStartPos)
    INSERT @tblList (IntValue) VALUES(CONVERT(INT, @strValue))
    SET @iStartPos = @iEndPos + 1
    SET @iEndPos = CHARINDEX(@strDelimiter, @strIntList, @iStartPos)
END
RETURN
END

Using the Function

......WHERE AuditID in 
 
(select * from dbo.fn_GetTableFromStrList(@StrAuditCaseID,',')))

Credit

We credit Scott Patterson for the code work.