briangriffin
Programmer
I need to pass in multiple values, and then execute a like statement for each value in the where clause:
Input values: '5','6','ICU'
and have the result resemble:
select * from units where unit like '%5%' or unit like '%6%' or unit like '%ICU%'
This is as far as I've gotten. The problem seems to be exiting the loop after the last value has been reached (and modifying this so that it still works if only one value is passed in).
Code:
-- Sample Data
drop table Units
create table Units
(unit varchar(10))
insert into units
select 'PEDS'
union
select '5E'
union
select '6W'
union
select 'ICU'
union
select 'NICU'
-- End Sample Data
declare @v_input varchar(50)
set @v_input = '6,IC,5'
declare @v_sql varchar(1000)
set @v_sql =
'select * from units where unit like ''%'' + LEFT(''' + @v_input + ''',charindex('','',''' + @v_input + ''') - 1) + ''%'''
set @v_input = RIGHT(@v_input, len(@v_input) - charindex(',',@v_input))
while charindex(',',@v_input) > 0
BEGIN
set @v_input = RIGHT(@v_input, len(@v_input) - charindex(',',@v_input))
set @v_sql = @v_sql + ' or unit like ''%'' + LEFT(''' + @v_input + ''',charindex('','',''' + @v_input + ''') - 1) + ''%'''
END
--select @v_sql
exec(@v_sql)
There may be some set based solution that eliminates the need for dynamic SQL, if so even better.
Thanks in advance for your help.