我写了一个存储过程在前台要调用有几个参数 要是前台不填的话就默认为全部
应该怎么写
比如where department= @department and -- 办事处查询
project_cstm.project_source_type_C= @project_source_type_C --- 行业
应该怎么写
比如where department= @department and -- 办事处查询
project_cstm.project_source_type_C= @project_source_type_C --- 行业
(project_cstm.project_source_type_C= @project_source_type_C OR @project_source_type_C IS NULL) --- 行业
project_cstm.project_source_type_C= ISNULL(NULLIF(@project_source_type_C,''),project_cstm.project_source_type_C)
--首先可以用like '%',比如
select ....
where department like @department ....,如果有值则传值,无值就传 %
--另外可以动态拼写sql ,
declare @sql varchar(2000)
select @sql = 'select ..... where 1=1 '
if(@department is not null)
select @sql = @sql + 'and department= @department '
.....
具体用哪种方式,看楼主的实际情况
create proc p_test @department varchar(100)='',@project_source_type_C varchar(100)=''
as
begin
select * from tb
where ( department = @department or @department='' or @department is null ) -- 办事处查询
and (project_source_type_C = @project_source_type_C or @project_source_type_C='' or @project_source_type_C is null) --- 行业
end
--可以有如下调用方式
p_test
p_test null
p_test 'sdf'
p_test null,null
p_test null,'dfg'
p_test 'sdf',null
p_test 'sdf','df'