sam 55243 (8/10/2016)
Hi All,I'm using stored procedure where i'm getting role Id in comma separated values and i need to check for specific role id in that. Im using charindex for this and wanted to know if this is best possible solution or is there any alternative
-- THIS IS FOR EXAMPLE
DECLARE @TradeRoles VARCHAR(100)
SET @TradeRoles = '1,2,3,4,5'
-- If the user has role of 1&4 only then Proceed for furhter processing
IF CHARINDEX('1',@TradeRoles) > 0 AND CHARINDEX('4', @TradeRoles) > 0
BEGIN
-- ADDITIONAL CODE
END
Please suggest
Nothing wrong with the splitter approach but you can resolve this just using CHARINDEX. The problem with the way you're doing it is that you can return false positives. Consider this code:
DECLARE @TradeRoles VARCHAR(100)
SET @TradeRoles = '100,2,3,4,5'
SELECT CHARINDEX('1',@TradeRoles);
This would work just fine:
IF CHARINDEX(',1,', ','+@TradeRoles+',') > 0 AND CHARINDEX(',4,', ','+@TradeRoles+',') > 0
BEGIN
PRINT 'yep!'
END
-- Itzik Ben-Gan 2001