22,209
社区成员
发帖
与我相关
我的任务
分享
CREATE FUNCTION wscfn_GetPersonInfoAndCustomInfo
(
@keyWords varchar(100)
)
RETURNS @temp table
(
personID uniqueidentifier ,
personName nvarchar(50),
departmentID uniqueidentifier,
departmentName nvarchar(255),
customID uniqueidentifier,
customName nvarchar(255),
accountID varchar(20)
)
AS
BEGIN
declare @sql varchar(5000)
set @sql = '
select p.personID,
p.CName PersonName,
d.departmentID,
d.DepartmentName,
c.CustomID,
c.CName CustomName,
a.AccountID
from TBCustomDepartment d,
TBPersonInDepartment pd,
TBPersonBaseInf p,
TBCustomBaseInf c,
TBAccountPerson a
where d.CustomID = c.CustomID
and d.DepartmentID = pd.DepartmentID
and pd.PersonID = p.PersonID
and a.PersonID = p.PersonID'
if(@keyWords != null and RTRIM(LTRIM(@keyWords)) !='' )
begin
if(@keyWords like 'w%' or @keyWords like 'W%' )
set @sql = @sql +' and a.AccountID like ''%'+@keyWords+'%'''
else
set @sql = @sql +' and c.CName like ''%'+@keyWords+'%'''
end
insert into @temp exec(@sql)
return
END
GO
alter FUNCTION wscfn_GetPersonInfoAndCustomInfo
(
@keyWords varchar(100)
)
RETURNS table
AS
BEGIN
declare @temp table
(
personID uniqueidentifier ,
personName nvarchar(50),
departmentID uniqueidentifier,
departmentName nvarchar(255),
customID uniqueidentifier,
customName nvarchar(255),
accountID varchar(20)
)
declare @sql varchar(5000)
set @sql = '
select p.personID,
p.CName PersonName,
d.departmentID,
d.DepartmentName,
c.CustomID,
c.CName CustomName,
a.AccountID
from TBCustomDepartment d,
TBPersonInDepartment pd,
TBPersonBaseInf p,
TBCustomBaseInf c,
TBAccountPerson a
where d.CustomID = c.CustomID
and d.DepartmentID = pd.DepartmentID
and pd.PersonID = p.PersonID
and a.PersonID = p.PersonID'
if(@keyWords != null and RTRIM(LTRIM(@keyWords)) !='' )
begin
if(@keyWords like 'w%' or @keyWords like 'W%' )
set @sql = @sql +' and a.AccountID like ''%'+@keyWords+'%'''
else
set @sql = @sql +' and c.CName like ''%'+@keyWords+'%'''
end
insert into @temp exec(@sql)
return @temp
END
GO
declare @temp table
(
personID uniqueidentifier ,
personName nvarchar(50),
departmentID uniqueidentifier,
departmentName nvarchar(255),
customID uniqueidentifier,
customName nvarchar(255),
accountID varchar(20)
)
insert into @temp exec(@sql)
return @temp