Question

sql存储过程中使用模糊查询

use MemoDatabase
if exists(select * from sysobjects where name = 'SelectAllProcess' )
drop Proc SelectAllProcess
go
create proc SelectAllProcess
(
@xingming varchar(15),
@chenghu varchar(15),
@haoma varchar(15),
@fenzu varchar(10)
)
as
begin
select xingming as 姓名,xingbie as 性别,chenghu as 称呼,haoma as 号码,fenzu as 分组,shijian as 时间 from MemoTable where xingming like '%'+@xingming+'%' or chenghu like '%'+@chenghu+'%' or haoma like '%'+@haoma+'%' or fenzu like '%'+@fenzu+'%'
end

exec SelectAllProcess @xingming='盼',@chenghu= '空格',@haoma='13720686947',@fenzu='空格'

谁能帮帮我啊！ 为什么执行的时候一定要给为空的参数一个空格，不给就查询出表里的全部内容了！
麻烦帮帮忙吧！
都不行啊！ 各位前辈啊！ 帮帮忙吧！

Answer 1

方法1:你可以给参数一定 默认值
create proc SelectAllProcess
@xingming varchar(15)='',
@chenghu varchar(15)='',
@haoma varchar(15)='',
@fenzu varchar(10)=''
。。。
exec SelectAllProcess
@xingming=default,
@chenghu= default,
@haoma=default,
@fenzu=default

或者用
if exists(select * from sysobjects where name = 'SelectAllProcess' )
drop Proc SelectAllProcess
go
create proc SelectAllProcess
@xingming varchar(15),
@chenghu varchar(15),
@haoma varchar(15),
@fenzu varchar(10)
as
begin
select xingming as 姓名,xingbie as 性别,chenghu as 称呼,haoma as 号码,fenzu as 分组,shijian as 时间
from MemoTable
where xingming like '%'+COALESCE(@xingming,'')+'%' or
chenghu like '睁粗%'+COALESCE(@chenghu,'')+'%' or
haoma like '%'+COALESCE(@haoma,'')+'%' or
fenzu like '悉此镇%'+COALESCE(@fenzu,'')+'扒搏%'
end

exec SelectAllProcess @xingming='盼',@chenghu=null,@haoma='13720686947',@fenzu=null

Answer 2

ALTER
PROCEDURE
[dbo].[pro_Search_keys]
@keys
nvarchar(max)
AS
BEGIN
SELECT*
FROM
text_Text
where
Title
like
‘%’亮凳御+@keys+‘粗乎%’敬岩
END

Answer 3

ISNULL(@fenzu,'') 每一个参数加上这个函数 就没有问题了

Answer 4

你的存储过程写的有问题。
你应该判断一下如果参数为空就不参加查询。