separating comma separated value in sql

Question

The SP takes the input value ListType_IDs which is a comma separated value of list types.

create procedure test
@ListType_IDs varchar(255) = null
as
declare @IsGetDirectReports bit
declare @IsGetDirectReportsManagers bit
declare @IsGetAllManagers bit
declare @IsGetFullTeamUnderManager bit
--I have to initialize the above flags based on the value of list type IDs

For example, let ListType_IDs=5,6,7

@IsGetDirectReports = 1, if @ListType_IDs has the value 5

@IsGetDirectReportsManagers = 1, if @ListType_IDs has the value 6

@IsGetFullTeamUnderManager = 1, if @ListType_IDs has the value 7

I need to achieve it using simple SQL code. Anybody please help out. Thanks in advance.

Why have you tagged Sql-Server 2005 **and** 2008? However, there are already many questions regarding this on SO. http://tinyurl.com/by4rst7 — Tim Schmelter, Jan 22 '13 at 13:04
Is that a typo? Should it be value 5 on all three assignments? — freefaller, Jan 22 '13 at 13:11
@freefaller : sorry i have wrongly entered the values...it will be 5,6,7... — prabu R, Jan 22 '13 at 13:12

score 2 · Accepted Answer · edited May 23 '17 at 12:03

2

IF CHARINDEX(',5,' , ',' +@ListType_IDs + ',') > 0
SELECT @IsGetIderctReports = 1

IF CHARINDEX(',6,' , ',' +@ListType_IDs + ',') > 0
SELECT @IsGetDirectReportsManagers = 1

IF CHARINDEX(',7,' , ',' +@ListType_IDs + ',') > 0
SELECT @IsGetFullTeamUnderManager = 1

Is storing a delimited list in a database column really that bad? Now we know.

edited May 23 '17 at 12:03

Community

1
1

answered Jan 22 '13 at 13:09

Anton Kovalenko

20,999
2
37
69

separating comma separated value in sql

1 Answers1