--先定义个function
Create FUNCTION SplitList
(@separator char(1), @List varchar(8000))
RETURNS @ReturnTable TABLE(ListItem varchar(1000) COLLATE Database_Default)
AS
BEGIN
DECLARE @Index int
DECLARE @NewText varchar(8000)
IF @List = null
RETURN
SET @Index = CHARINDEX(@separator, @List)
WHILE NOT(@Index = 0)
BEGIN
SET @NewText = RTRIM(LTRIM(LEFT(@List, @Index - 1)))
SET @List = RIGHT(@List, LEN(@List) - @Index)
INSERT INTO @ReturnTable(ListItem) VALUES(@NewText)
SET @Index = CHARINDEX(@separator, @List)
END
INSERT INTO @ReturnTable(ListItem) VALUES(RTRIM(LTRIM(@List)))
RETURN
END
--在定义存储程序
create table #temp (...) -- 与表categories结构一致
declare @str nvarchar(100),@Item nvarchar(100)
set @str='be,con'
declare c1 cursor scroll for
select listItem from SplitList(',',@str) T
open c1
fetch first from c1 into @Item
while @@fetch_status=0
begin
insert into #temp
select * from categories where categoryname like ('%'+@str +'%')
fetch next from c1 into @Item
end
select * from #temp
close c1
deallocate c1