多关键词存储过程解决方案-MSSQL教程-爱易网页

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

日期：2014-05-18　浏览次数：20567 次

多关键词存储过程
过一个关键词没有问题，多个关键词就返回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)
)
as
Begin
    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+'%'    
    End
End

------解决方案--------------------
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)