SQL Server - Dynamic Comma Delimited Values in IN Codition inside SSIS OleDB Editor

Asked By avula on 21-Nov-14 08:06 AM
    DECLARE @Name = 'Robin,Jackson'
    or
    DECLARE @Name as Varchar(100)
   SELECT @Name  = Names from StudentNames

i have configured @Name names in table and given front end screen to change

    SELECT * FROM Student
       WHERE SudentName IN (@Names)

   this query not working inside SSIS Oledb editior
Robbe Morris replied to avula on 21-Nov-14 08:05 AM
SQL doesn't support using the IN clause on a delimited list of variable values.  Many have tried.  So, don't feel bad.  You'll need to load each of the values in your delimited list into a SQL Server TABLE variable and perform your IN clause against it.

 where SomeColumn in (select someOthercolumn from @MyTableVariable))
najna abdulla replied to avula on 26-Nov-14 11:57 PM
najna abdulla replied to avula on 26-Nov-14 11:58 PM
create procedure sp_ForStudent
as begin
declare @index int,@slice nvarchar(100),@commastring nvarchar(100),@name nvarchar(100),@col nvarchar(max)
set @commastring="" ,@name='Robin,Jackson'
select @index=1
while @index!=0

begin
select @index=CHARINDEX(',',@name)
if(@index!=0)
select @slice=left(@name,@inedex-1)

else
select @slice=@name
if(@index=0)
set @commastring=@Commastring +' '' ' + @slice+ ' '' '

else
set @commastring=@Commastring +' '' ' + @slice+ ' '' '+ ','

set @name=right(@name,len(@name)-@index)

if(len(@name)=0
break
end
 
set @col=N' SELECT * FROM Student
       WHERE SudentName IN ( '+@commastring+')''
exec(@col)
end



exec sp_ForStudent