Passing Comma Separated Values to SQL Server 2005

Here is the sample code.. to store Comma Separated values into a Temp Table

To Pass Comma Delimited Values into a Sql Server Table.


declare @CompanyIds varchar(100)
set @CompanyIds = '1,2,3,4,5,6,7,8,9,100,20,400,60'

declare @tblCompanyIDs table
(
tblCompanyIDs int
)

DECLARE @String VARCHAR(10)
WHILE LEN(@CompanyIDs) > 0
BEGIN
SET @String = LEFT(@CompanyIDs,
ISNULL(NULLIF(CHARINDEX(',', @CompanyIDs) - 1, -1),
LEN(@CompanyIDs)))
SET @CompanyIDs = SUBSTRING(@CompanyIDs,
ISNULL(NULLIF(CHARINDEX(',', @CompanyIDs), 0),
LEN(@CompanyIDs)) + 1, LEN(@CompanyIDs))

INSERT INTO @tblCompanyIDs
VALUES ( @String )
END

select * from @tblCompanyIDs order by tblCompanyIDs

No comments: