多关键词 存储过程解决方案

多关键词 存储过程
过一个关键词没有问题，多个关键词就返回0，关键词之间用|间隔， 帮我看看该怎么改 对单引号理解不深入
ALTER procedure [dbo].[searchnum] 
(@keyword nvarchar(200)
)
as

if(CHARINDEX('|',@keyword)>0)
select count(*) from product where pname like '%'+replace(@keyword,'|','%'' and pname like ''%')+'%'
else
select count(*) from product where pname like '%'+@keyword+'%'
return


[解决办法]

SQL code

Alter Proc [dbo].[searchnum]  (@keyword nvarchar(200))asBegin    If ( CHARINDEX('|',@keyword)>0)    Begin        declare @sql nvarchar(2000)        Create table #table(col varchar(20))        set @sql='insert into #table(col) select '+replace(@keyword,'|',' col union all select ')        Execute(@sql)        select count(*) from product where EXISTS   (select * from #table)        Drop table #table     End    Else    Begin     select count(*) from product where pname  like '%'+@keyword+'%'        EndEnd
[解决办法]
SQL code
declare @keyword  varchar(max)set @keyword='儿童|玩具'with product  as(    select '儿童' as pname union all    select '玩具' union all     select '物品' union all    select '食品')--把查询where条件反写select * from product where @keyword like '%'+pname+'%'--pname---------儿童--玩具--(2 row(s) affected)