Click here to Skip to main content
Click here to Skip to main content

SQL - Converting a varchar parameter to a table

, 3 Dec 2012
Rate this:
Please Sign up or sign in to vote.
Create a table from a Varchar list value

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Glagace_7763
Software Developer Hayes Management Consulting
United States United States
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web02 | 2.8.140709.1 | Last Updated 3 Dec 2012
Article Copyright 2012 by Glagace_7763
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid