CODEWITHSUNDEEP
sql-servert-sqlparameters
R.Merritt
R.Merritt

Reputation: 477

formatting a string param to be the IN statement

I have a bit of code:

declare @GroupNames nvarchar(1024)
EXEC Utility.dbo.Get_ADGroups_ForUser 'rwm132' ,@GroupNames output
print @GroupNames

the print statement looks like this :

'vQAHR','vQAResearch','vQAICT','vQAAdvancement','vAllResearch','vAllStudent','vQATeachLearn','vQAFinance','vQAHR'

(0 row(s) affected)

I have a predicate

WHERE
     (
RLP.ALL_GROUP_NAME IN  ( @GroupNames )  
 )

this doesn't seem to work how should I format the string with ' so that it works. If I copy that string and paste it in place of @GroupNames in the predicate it works file just something in the substitution that seems to screw it up.

Upvotes: 2

Views: 79

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81930

If not 2016, you can use a TVF to split or parse the string

-- Notice the single quotes surrounding each item are not necessary 
Declare @GroupNames varchar(max) = 'vQAHR,vQAResearch,vQAICT,vQAAdvancement,vAllResearch,vAllStudent,vQATeachLearn,vQAFinance,vQAHR'

Select A.* 
 From  YourTable A
 Where RLP.ALL_GROUP_NAME in (Select RetVal from [dbo].[udf-Str-Parse](@GroupNames,','))

The UDF if needed

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From (Select x = Cast('<x>'+ Replace(@String,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')

Now, Just for fun, if you don't want a UDF, you can

Select A.*
 From  YourTable A
 Where RLP.ALL_GROUP_NAME In (Select Item=LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                                From (Select x = Cast('<x>'+ Replace(@GroupNames,',','</x><x>')+'</x>' as xml).query('.')) as A 
                                Cross Apply x.nodes('x') AS B(i)
                             )

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

You need a split string function

In Sql Server 2016 you can use STRING_SPLIT function 

WHERE
(
RLP.ALL_GROUP_NAME IN  ( select value from STRING_SPLIT(@GroupNames,','))  
 )

For previous version use any one of the method from below link

Split strings the right way – or the next best way

In case you are appending single quotes for each value inside the string then use this

WHERE
(
RLP.ALL_GROUP_NAME IN  ( select stuff(stuff(value,1,1,''),len(value)-1,1,'') from STRING_SPLIT(@GroupNames,','))  
 )

or you can use Dynamic Sql, Considering there wont be any Sql injection since it is a procedure output

declare @sql varchar(max)=''
set @sql ='
select ..
WHERE
RLP.ALL_GROUP_NAME IN  ( '+@str+') '

exec (@sql)

Upvotes: 2

Related Questions