CODEWITHSUNDEEP
sqlsql-server
prabu R
prabu R

Reputation: 2209

separating comma separated value in sql

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.

Upvotes: 1

Views: 376

Answers (1)

Anton Kovalenko
Anton Kovalenko

Reputation: 21507

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.

Upvotes: 2

Related Questions