sqlserver存储过程的like模糊查询问题

按多条件模糊查询，比如快捷搜索，可同时输入一个或多个关键字进行查询。
传入的参数是一个字符串，右一个或多个关键字和空格组成，
存储过程中需要完成：
按空格拆分字符串，并查询符合条件的记录
代码如下：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Spid] 
@intParentID INT,
@intRoleID INT,
@QuickselectStr nvarchar(100)//关键字和空格组成的字符串，长度不定
AS
BEGIN
SET NOCOUNT ON

  -- 如果角色ID为-1，表示用户身份为Guest，可以浏览除了系统管理菜单之外的全部菜单  
IF(@intRoleID=-1)
BEGIN
  SELECT * FROM MenuInfo 
WHERE ParentID=@intParentID AND ParentID!=-1 AND MenuDes like '%'关键字'%' ORDER BY menuorder
END

SET NOCOUNT OFF
END


该怎么写？没分了，都给你，在线等

将你传入的参数拆分到一个临时表里面 在做查询。

SQL code

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,name varchar(8))
insert into #T
select 1,'jame,job' union all
select 2,'paul,mc' union all
select 3,'carl';

--> 1. CTE 递归找分隔字符位置法：速度极快
with T (id,P1,P2) as
(
    select id,charindex(',',','+name),charindex(',',name+',')+1 from #T
    union all
    select a.id,b.P2,charindex(',',name+',',b.P2)+1 from #T a join T b on a.id=b.id where charindex(',',name+',',b.P2)>0
)
select a.id,name=substring(a.name+',',b.P1,b.P2 - b.P1 - 1) from #T a join T b on a.id=b.id order by 1
/*
id          name
----------- ---------
1           jame
1           job
2           mc
2           paul
3           carl
*/
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,name varchar(8))
insert into #T
select 1,'jame,job' union all
select 2,'paul,mc' union all
select 3,'carl';

--> 2. 临时表法：速度比CTE方法相差无几
if object_id('tempdb.dbo.#') is not null drop table #
select top 8000 id=identity(int,1,1) into # from syscolumns,sysobjects --> select max(len(name)) from #T=11
select a.id,name=substring(a.name+',',b.id,charindex(',',a.name+',',b.id+1)-b.id) from #T a inner join # b on substring(','+a.name,b.id,1)=','
/*
id          name
----------- ---------
1           jame
1           job
2           mc
2           paul
3           carl
*/

3. XML法：速度较慢
select
    a.id,b.name
from
    (select id,name=convert(xml,'<root><v>'+replace(name,',','</v><v>')+'</v></root>') from #T ) a
outer apply
    (select name=N.v.value('.','varchar(100)') from a.name.nodes('/root/v') N(v)) b
/*
id          name
----------- ---------
1           jame
1           job
2           mc
2           paul
3           carl
*/

我看看啊，

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Sp_Frame_Menu_QuickSelectGetMenuByRoleID] 
-- Add the parameters for the stored procedure here
@intParentID INT,
@intRoleID INT,
@QuickselectStr nvarchar(100)//传入的关键字和空格组成的字符串
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

  -- Insert statements for procedure here
-- 如果角色ID为-1，表示用户身份为Guest，  
IF(@intRoleID=-1)
BEGIN
  SELECT * FROM Tb_Frame_MenuInfo 
WHERE ParentID=@intParentID AND ParentID!=-1 AND MenuDes like '%'+@QuickselectStr+'%' ORDER BY menuorder
END
-- 如果角色ID为-2，表示用户身份为Manager
ELSE IF(@intRoleID=-2)
BEGIN
SELECT * FROM Tb_Frame_MenuInfo 
WHERE ParentID=@intParentID AND MenuDes like'%'+@QuickselectStr+'%' ORDER BY menuorder
END
ELSE
BEGIN
SELECT * FROM Tb_Frame_MenuInfo 
WHERE Parentid=@intParentID AND MenuDes like'%'+@QuickselectStr+'%' AND MenuID IN
(SELECT MenuID FROM Tb_Frame_PermissionInfo WHERE PermissionID IN 
(SELECT PermissionID FROM Tb_Frame_RolePermissionInfo WHERE RoleID=@intRoleID))
ORDER BY menuorder
END

SET NOCOUNT OFF
END

上面代码是我的只输入一个查询条件时的存储过程，能帮我改下吗高手，我会好好学习的。