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

String.zip

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