ALTER PROCEDURE [dbo].[P_HR_getEmpDataMohu]
@dwName nvarchar(20)=null,
@bmName nvarchar(20)=null,
@gzName nvarchar(20)=null,
@gwName nvarchar(20)=null,
@zwName nvarchar(20)=null,
@empName nvarchar(20)=null,
@empState nvarchar(20)=null
AS
BEGIN
SET NOCOUNT ON;
select * from V_HR_emp where
dwName like '%'+@dwName+'%'
and bmName like '%'+@bmName+'%'
and gzName like '%'+@gzName+'%'
and gwName like '%'+@gwName+'%'
and zwName like '%'+@zwName+'%'
and empName like '%'+@empName+'%'
and empState like '%'+@empState+'%'
END如果视图V_HR_emp里面的某个字段的值为空,如bmName为空,则select不出那一条记录,如何修改才能把它也select出来呢?
@dwName nvarchar(20)=null,
@bmName nvarchar(20)=null,
@gzName nvarchar(20)=null,
@gwName nvarchar(20)=null,
@zwName nvarchar(20)=null,
@empName nvarchar(20)=null,
@empState nvarchar(20)=null
AS
BEGIN
SET NOCOUNT ON;
select * from V_HR_emp where
dwName like '%'+@dwName+'%'
and bmName like '%'+@bmName+'%'
and gzName like '%'+@gzName+'%'
and gwName like '%'+@gwName+'%'
and zwName like '%'+@zwName+'%'
and empName like '%'+@empName+'%'
and empState like '%'+@empState+'%'
END如果视图V_HR_emp里面的某个字段的值为空,如bmName为空,则select不出那一条记录,如何修改才能把它也select出来呢?
就行了.
(dwName like '%'+@dwName+'%' or dwName is null)
isnull(dwNamedwName like '%'+@dwName+'%'
and isnull(bmName,@bmName) like '%'+@bmName+'%'
and isnull(gzName,@gzName) like '%'+@gzName+'%'
and isnull(gwName,@gwName) like '%'+@gwName+'%'
and isnull(zwName,@zwName) like '%'+@zwName+'%'
and isnull(empName,@empName) like '%'+@empName+'%'
and isnull(empState,@empState) like '%'+@empState+'%'
select * from V_HR_emp where
isnull(dwName,@dwName) like '%'+@dwName+'%'
and isnull(bmName,@bmName) like '%'+@bmName+'%'
and isnull(gzName,@gzName) like '%'+@gzName+'%'
and isnull(gwName,@gwName) like '%'+@gwName+'%'
and isnull(zwName,@zwName) like '%'+@zwName+'%'
and isnull(empName,@empName) like '%'+@empName+'%'
and isnull(empState,@empState) like '%'+@empState+'%'
dwName like isnull('%'+@dwName+'%',dwname)
and bmName like isnull('%'+@bmName+'%',bmname)
and gzName like isnull('%'+@gzName+'%',gzname)
and gwName like isnull('%'+@gwName+'%',gwname)
and zwName like isnull('%'+@zwName+'%',zwname)
and empName like isnull('%'+@empName+'%',empname)
and empState like isnull('%'+@empState+'%',empstate)
(dwName like '%'+@dwName+'%' or dwName is null)
and (bmName like '%'+@bmName+'%' or bmName is null)
and (gzName like '%'+@gzName+'%' or gzName is null)
and (gwName like '%'+@gwName+'%' or gwName is null)
and (zwName like '%'+@zwName+'%' or zwName is null)
and (empName like '%'+@empName+'%' or empName is null)
and (empState like '%'+@empState+'%' or empState is null)