Ndtyjky

I want to find if a string is present among a collection of string in sql .
Given code is always returning false.Can anyone plz tell me where am i wrong ?

alter procedure is_exception_user @usrid varchar(100)

as

begin

if @usrid in ('abc,xyz')

SELECT 'TRUE'

ELSE 

SELECT 'FALSE'

END



exec is_exception_user 'xyz'

Is this the logic you want?

if @usrid in ('abc', 'xyz')

    select 'TRUE'

else 

    select 'FALSE';

Or more simply:

select (case when @usrid in ('abc', 'xyz') then 'TRUE' else 'FALSE' END)

You can do:

IF ',' + 'abc,xyz' + ',' LIKE '%,' + @usrid + ',%'

BEGIN

  SELECT 'TRUE';

END

The trick with the leading and trailing commas in both sides of the comparison serves two purposes: (1) to make sure '12' doesn't match '34,124' and (2) to catch when the one you're looking for is at the beginning and end of the string.

And I wrote this this way:

',' + 'abc,xyz' + ','

Because I assume you're not going to hard-code that big comma-separated string in your procedure and instead it's going to be derived from a column or a variable.

In SQL Server 2016, you can instead do this:

IF @usrid IN (SELECT Value FROM STRING_SPLIT('abc,xyz', ','))

BEGIN

  SELECT 'TRUE';

END

Or just store your values in a table structure in the first place:

DECLARE @values TABLE(Value varchar(32) PRIMARY KEY);

INSERT @values VALUES('abc','xyz');



IF @usrid IN (SELECT Value FROM @values)

BEGIN

  SELECT 'TRUE';

END

Sets of individual values are much better in SQL Server than any kind of string or other structure that holds multiple values. Each piece of data should be its own fact. Combining them for convenience leads to pain at some point.