在存储过程中的参数的问题

Question

create table #tableTest(id int identity , name varchar(20),age int,)
go
insert into #tableTest
select '小明',23 union all
select '小红',28 union all
select '小军',27
go
select *from #tableTest
go
create proc procTest
@name varchar(20),
@age int,
@IDs varchar(30)
as
begin
select *from #tableTest where 1=1
end--当我传入@name参数等于 小明,23岁，还有ID在（1,3）的时候
--我怎么可以弄成可选的参数
--比如，name不为空时候
select *from #tableTest where 1=1 and name like '小明'
--如果name参数为空的时候，IDs参数不为空的时候
select *from #tableTest where 1=1 and id in(1,3)
--请问一下，就有参数不为空的时候存储过程中的SQL追加条件，为空的时候就不追加，这样带可选参数的存储过程怎么写，以及怎么调用，请帮小弟写一个实例

Answer 1

create table #tableTest(id int identity , name varchar(20),age int,)
go
insert into #tableTest
select '小明',23 union all
select '小红',28 union all
select '小军',27
go
select *from #tableTest
gocreate proc procTest
@name varchar(20)='小明',
@IDs varchar(30)='1,3'
as
set nocount on select * 
from #tableTest  
where 1=1 
and case when len(@name)>0 then charindex(@name,name) else 1 end > 0   -->@name参数为空或者不为空都可以
and case when len(@IDs)>0 then charindex(','+cast(id as varchar)+',',','+@IDs+',') else 1 end > 0 -->@IDs参数为空或者不为空都可以
go
-----测试执行
exec procTest/*
1 小明 23
*/exec procTest ''/*
1 小明 23
3 小军 27
*/exec procTest '','2'/*
2 小红 28
*/

Answer 2

drop table #tableTestcreate table #tableTest(id int identity , name varchar(20),age int,)
go
insert into #tableTest
select '小明',23 union all
select '小红',28 union all
select '小军',27
go
select *from #tableTest
go
create proc procTest
@name varchar(20)=null,
@age int = null,
@IDs varchar(30) = null
as--当我传入@name参数等于 小明,23岁，还有ID在（1,3）的时候
--我怎么可以弄成可选的参数
--比如，name不为空时候
if @name is not null
begin
   select *from #tableTest
   where charindex(@name,name)>0 
         and case when @age is null 
                       then 1 
                  else @age 
             end = case when @age is null
                             then 1
                        else age
                   end
end
else if @name is null and @ids is not null
begin
   select *from #tableTest 
   where 1=1 and charindex(','+@ids+',',','+cast(id as varchar)+',') >0
end
else 
   select *from #tableTestgo
--全部数据
exec procTest 
/*
id name age
1 小明 23
2 小红 28
3 小军 27
*/
 
 
 
exec procTest '小明',23
/*
1    小明    23
3    小军    27
*/
 
exec procTest @ids = '2'
/*
2    小红    28

Answer 3

你好，我想说的是，我的那个#tableTest 表是很多SQL拼装出来的，很多代码，你不可能叫我根据if else判断写很多次吧？我想要的只是如果就是根据参数改为where后面的条件就额可以了，不用写很多次查询语句。大哥还有别 的办法吗？

Answer 4

你好大侠，我的如果name参数不为空 可是在where条件上加上像这样的喔，name like '参数值' ，如果ID 不为空可是加成这样的喔， id in(ID参数值) ，

Answer 5

name like '参数值' 和name = '参数值' 是一样的.
如果为空就等于原来值,不为空就等于参数值,我用charindex()函数可以匹配模湖查询.不想匹配模湖查询就改下:
name=case when @name='' then name else @name end---->如果ID 不为空可是加成这样的喔， id in(ID参数值) ， 
我那样写没问题:create proc procTest
@name varchar(20)='小明',
@IDs varchar(30)='1,3'
as
set nocount on select * 
from #tableTest  
where 1=1 
and name=case when @name='' then name else @name end   
and case when len(@IDs)>0 
then charindex(','+cast(id as varchar)+',',','+@IDs+',') else 1 end > 0 
go

Answer 6

create table #tableTest(id int identity,name varchar(20),age int,)insert into #tableTest
select '小明',23 union all
select '小红',28 union all
select '小军',27
create proc procTest
(@name varchar(20),
 @age int,
 @IDs varchar(30))
as
begin
 declare @tsql varchar(6000) select @tsql='select * from #tableTest '
             +' where 1=1 '
             +case when @name<>'' then ' and name like '''+@name+''' ' else '' end
             +case when @IDs<>'' then ' and id in('+@IDs+') ' else '' end
             
 exec(@tsql)
end
-- 测试1
exec procTest @name='小明',@age=null,@IDs=''
/*
id          name                 age
----------- -------------------- -----------
1           小明                   23(1 row(s) affected)
*/
-- 测试2
exec procTest @name='',@age=null,@IDs='1,3'
/*
id          name                 age
----------- -------------------- -----------
1           小明                   23
3           小军                   27(2 row(s) affected)
*/
-- 测试3
exec procTest @name='小明',@age=null,@IDs='1,3'
/*
id          name                 age
----------- -------------------- -----------
1           小明                   23(1 row(s) affected)
*/

Answer 7

大侠，如果我name是前模糊呢？你怎么弄？你那个是全模糊吧，如果我的是'%李'，或者是'李%'你怎么办？

Answer 8

具体问题具体分析,看实际需要,如果要求特殊,那么就生成动态@sql语句,再执行 exec (@sql)
中间有什么特殊要求,就用语句语句判断

Answer 9

你好，我想说的是，我的那个#tableTest 表是很多SQL拼装出来的，很多代码，你不可能叫我根据if else判断写很多次吧？我想要的只是如果就是根据参数改为where后面的条件就额可以了，不用写很多次查询语句。大哥还有别 的办法吗？其实我觉得你这种情况，还是做成动态语句比较合适，大致的语句：drop table #tableTestcreate table #tableTest(id int identity , name varchar(20),age int,)
go
insert into #tableTest
select '小明',23 union all
select '小红',28 union all
select '小军',27
go
select *from #tableTest
go
alter proc procTest
@name varchar(20)=null,
@age int = null,
@IDs varchar(30) = null
as--当我传入@name参数等于 小明,23岁，还有ID在（1,3）的时候
--我怎么可以弄成可选的参数
--比如，name不为空时候declare @sql nvarchar(max);set @sql = '';set @sql = 'select * from #tableTest where 1 = 1';set @sql = @sql + 
           case when @name is not null
                     then ' and name like ' + QUOTENAME(@name +'%','''')
                when @age is not null
                     then ' and age = ' + cast(@age AS varchar)
                when @ids Is not null
                     then ' and id in (' + @ids +')'
                else ' '
           end--打印出语句
select @sql  as '语句'--执行语句
--exec(@sql)go
exec procTest
/*
语句
select * from #tableTest where 1 = 1 
*/exec procTest '小明',23
/*
语句
select * from #tableTest where 1 = 1 and name like '小明%'
*/
exec procTest @ids = '2,3'
/*
语句
select * from #tableTest where 1 = 1 and id in (2,3)
*/

Answer 10

把动态语句和case when相结合，不用if-else这种结构， 
那么总体上看起来，不仅很简洁，而且不易出错，输出语句后，很容易知道是那一部分出错了，另外，通过case when很容易再加上N多个查询条件，结构简单，而且很清晰，比如你要加一个条件，那么加一个when 就可以：
--drop table #tableTestcreate table #tableTest(id int identity , name varchar(20),age int,)
go
insert into #tableTest
select '小明',23 union all
select '小红',28 union all
select '小军',27
go
select *from #tableTest
gocreate proc procTest
@name varchar(20)=null,@age int = null,@IDs varchar(30) = null
asdeclare @sql nvarchar(max);
set @sql = '';set @sql = 'select * from #tableTest where 1 = 1';set @sql = @sql + 
           case when @name is not null
                     then ' and name like ' + QUOTENAME(@name +'%','''')
                when @age is not null
                     then ' and age = ' + cast(@age AS varchar)
                when @ids Is not null
                     then ' and id in (' + @ids +')'
                else ' '
           end--打印出语句
select @sql  as '语句'--执行语句
--exec(@sql)
go
exec procTest
/*
语句
select * from #tableTest where 1 = 1 
*/exec procTest '小明',23
/*
语句
select * from #tableTest where 1 = 1 and name like '小明%'
*/
exec procTest @ids = '2,3'
/*
语句
select * from #tableTest where 1 = 1 and id in (2,3)
*/

Answer 11

create proc procTest @name varchar(20), @age int, @IDs varchar(30) 
as
begin
declare @sql varchar(8000)
set @sql =' select *from #tableTest where 1=1 '
if @name is not null 
begin
set @sql = @sql + ' and name ='''+@name+''''
endif @id is not null
begin
set @sql = @sql + ' and id in ('+@id+')'
end
end

Answer 12

我已经解决了，谢谢大家ALTER proc [dbo].[proGroupCollectionExport3]
@StrtTime nvarchar(12)='1900-1-1',--开始时间
@EndTime nvarchar(12)='2100-1-1',--结束时间
@CaseIDs nvarchar(2000)=null,--案件ID集合字符串比如'1,3,5'
@CardIDs nvarchar(2000)=null,--卡ID集合字符串比如'1,2,4'
@BatchCode nvarchar(50)='',--批次编号全匹配
@BranchID int=0,--机构ID
@Name nvarchar(15)=''--债务人名字全匹配
--调用说明
--Exec proGroupCollectionExport3 无条件
--Exec proGroupCollectionExport3 '1900-1-1','2100-1-1','3,6' 传案件ID集合
--Exec proGroupCollectionExport3 '1900-1-1','2100-1-1','','2,8' 传卡ID集合
--Exec proGroupCollectionExport3 '1900-1-1','2100-1-1','','','YLC2013052201' 传批次编号
--Exec proGroupCollectionExport3 '1900-1-1','2100-1-1','','','','3' 传机构ID
--Exec proGroupCollectionExport3 '1900-1-1','2100-1-1','','','','','李桐'传债务人姓名
as
begin
select case_id,operate_date,外访情况,电催情况,备注新增查找信息 into #tb from 
(SELECT     t_pr.case_id, t_pr.operate_date,
  外访情况= CONVERT(nvarchar(max),''),
  电催情况=CONVERT(nvarchar(max),case [operator_name] when '电催' then CONVERT(varchar(12) ,t_pr.[operate_date], 111 )+'  '+CONVERT(varchar(12) ,t_pr.[operate_date], 108 )+',  致电：'+obj.[name]+'('+obj.[relation_name]+') '+t_pr.[object_content]+'  状态：'+dic.[item_name]+'  内容：'+t_pr.[progress_content]+' ' else ''end),
  备注新增查找信息=CONVERT(nvarchar(max),case [operator_name] when '资料更新' then CONVERT(varchar(12) ,t_pr.[operate_date], 111 )+'  '+CONVERT(varchar(12) ,t_pr.[operate_date], 108 )+',  更新内容：'+t_pr.[progress_content]+' ' else ''end)
FROM       t_progress AS t_pr LEFT JOIN
           t_phone obj ON obj.phone_id = t_pr.object_id LEFT OUTER JOIN          
           t_user AS u ON u.user_id = t_pr.operator_id AND u.is_deleted = 0 left outer join
           t_dict_item dic on item_value=t_pr.contact_ret and dic.dict_code='ContactResult' and dic.is_deleted=0
WHERE     t_pr.is_deleted = 0  --UNION
--SELECT     t_le.case_id, t_le.applicant, t_le.apply_date AS operate_date, '信函' AS operate_name, isnull(obj.type_name,'') as type_name,obj.[address] AS object_content, isnull(obj.name,'') as name,isnull(obj.relation_name,'未知') as relation_name, '申请信函' AS progress_content, 0 AS contact_ret,0 as promised_amount, 0 as promised_date
--FROM       t_letter t_le LEFT OUTER JOIN
--           t_address obj ON obj.address_id = t_le.address_id
--WHERE     t_le.is_deleted = 0UNION
SELECT    t_vi.case_id, t_vp.operate_date,
  外访情况= CONVERT(nvarchar(max),CONVERT(varchar(12) ,t_vp.[operate_date], 111 )+'  '+CONVERT(varchar(12) ,t_vp.[operate_date], 108 )+', 外访'+obj.[type_name]+'：'+ obj.[address]+', 外访内容：'+t_vp.[operate_content]+' '),
  电催情况= CONVERT(nvarchar(max),''),
  备注新增查找信息= CONVERT(nvarchar(max),'')
FROM      t_visit_progress t_vp LEFT OUTER JOIN
          t_visits t_vi ON t_vi.visit_id = t_vp.visit_id LEFT OUTER JOIN
           t_address obj ON obj.address_id = t_vi.address_id
WHERE     t_vp.is_deleted = 0 AND t_vi.is_deleted = 0 AND t_vp.operate_type IN ('外访登记'))t 
where t.operate_date between CONVERT(date,@StrtTime) and CONVERT(date,@EndTime)--时间条件
select 
姓名=isnull(t_bu.name,''),
身份证号=isnull(t_bu.id_no,''),
委案日期=t_ba.entrust_date,
委托时逾期天数=isnull(c_ca.overdue_days,''),
外包公司='',
委案金额=isnull(c_ca.collect_amount,0.00),
还款金额=isnull(c_ca.repayment,0.00),
个案状态列=ISNULL(t_dic.item_name,''),
外访情况=isnull(TableA.外访情况,''),
电催情况=isnull(TableA.电催情况,''),
备注新增查找信息=isnull(TableA.备注新增查找信息,'')
from (select case_id,
stuff(( select case z.外访情况 when '' then '' else z.外访情况+' ；' end  from #tb z where   t.case_id=z.case_id for xml path('')), 1, 0, '')  外访情况,
stuff(( select case z.电催情况 when '' then '' else z.电催情况+ ' ；' end from #tb z where   t.case_id=z.case_id for xml path('')), 1, 0, '')  电催情况,
stuff(( select case z.备注新增查找信息 when '' then '' else z.备注新增查找信息+' ；' end  from #tb z where  t.case_id=z.case_id for xml path('')), 1, 0, '')  备注新增查找信息
from #tb t  
group by t.case_id)TableA
left outer join t_business_case t_bu on t_bu.case_id=TableA.case_id
left outer join t_batch t_ba on t_bu.batch_id=t_ba.batch_id
left outer join t_dict_item t_dic on t_dic.item_value=t_bu.collect_status and t_dic.dict_code='CollectionStatus' and t_dic.is_deleted=0 
left outer join t_card_info c_ca on TableA.case_id=c_ca.case_id
where 1=1
and case when len(@CaseIDs)>0  then charindex(','+cast(TableA.case_id as nvarchar(10))+',',','+@CaseIDs+',') else 1 end > 0--案ID件条件
and case when len(@CardIDs)>0  then charindex(','+cast(c_ca.card_id as nvarchar(10))+',',','+@CardIDs+',') else 1 end > 0--卡ID条件
and t_ba.batch_code=case when @BatchCode='' then t_ba.batch_code else @BatchCode end--批次条件
and t_bu.rcpt_branch=case when @BranchID=0 then t_bu.rcpt_branch else @BranchID end--机构条件
and t_bu.name=case when @Name='' then t_bu.name else @Name end--债务人条件
endGO

Answer 13

这样实行，但是如果数量量一上去，查询性能会下降，因为你的语句在where里面写了一堆的case when，我以前也这么写过，但数据量一上去，就不行，后来还是得改