SQL Server Functions and views

-- Search fields to be set: expertname, expertdomain, hitcountsdata, workhistory, eduhistory, projecthistory, award, researchpaper, researchpapermonograph, expertallinfo
-- The Field expertallinfo is of the document type, and all other retrieval fields are of the phrase type.

-- 1. obtain expert experience
-- Drop function f_getworkhistory

Create Function f_getworkhistory (
@ Expertid numeric (9)
)
Returns varchar (8000)
As
Begin
Declare @ RET varchar (8000)
Select @ ret =''
Select @ ret = @ RET + isnull (workorgnization, '') +'' + isnull (workpost, '') + ';' from xwcmworkhistory
Where objid = @ expertid and objtype = 2045598757
If (@ RET is null)
Return''
Return @ RET
End
Go

-- 2. obtain expert learning experience
-- Drop function f_geteduhistory
Create Function f_geteduhistory (
@ Expertid numeric (9)

)
Returns varchar (8000)
As
Begin
Declare @ RET varchar (8000)
Select @ ret =''
Select @ ret = @ RET + isnull (eduschool, '') +'' + isnull (professionalname, '') + ';' from xwcmeduhistory
Where objid = @ expertid and objtype = 2045598757
If (@ RET is null)
Return''
Return @ RET
End
Go

-- 3. obtain expert project experience
-- Drop function f_getprojecthistory
Create Function f_getprojecthistory (
@ Expertid numeric (9)

)
Returns varchar (8000)
As
Begin
Declare @ RET varchar (8000)
Select @ ret =''
Select @ ret = @ RET + isnull (projectname, '') +'' + isnull (post, '') +'' + isnull (orgnization ,'') + ''+ isnull (production,'') + ';' from xwcmprojecthistory
Where objid = @ expertid and objtype = 2045598757
If (@ RET is null)
Return''
Return @ RET
End
Go

-- 4. obtain experts' Awards
-- Drop function f_getaward
Create Function f_getaward (
@ Expertid numeric (9)

)
Returns varchar (8000)
As
Begin
Declare @ RET varchar (8000)
Select @ ret =''
Select @ ret = @ RET + isnull (awardtitle, '') + ';' from xwcmaward
Where objid = @ expertid and objtype = 2045598757
If (@ RET is null)
Return''
Return @ RET
End
Go

 

-- 5. obtain expert papers
-- Drop function f_getresearchpaper
Create Function f_getresearchpaper (
@ Expertid numeric (9)
)
Returns varchar (8000)
As
Begin
Declare @ RET varchar (8000)
Select @ ret =''
Select @ ret = @ RET + isnull (researchpapertitle, '') +'' + isnull (author, '') +'' + isnull (summary ,'') + ''+ isnull (periodicalname,'') +''
+ Isnull (publishcompany, '') +'' + isnull (periodtime, '') +'' + isnull (content, '') + ';' from xwcmresearchpaper
Where researchpaperid in (select researchpaperid from xwcmresearchpaperuser
Where objid = @ expertid and objtype = 2045598757
) And researchpapertype = 0

If (@ RET is null)
Return''
Return @ RET
End
Go

-- 6. obtain expert Monographs
-- Drop function f_getresearchpapermonograph
Create Function f_getresearchpapermonograph (
@ Expertid numeric (9)
)
Returns varchar (8000)
As
Begin
Declare @ RET varchar (8000)
Select @ ret =''
Select @ ret = @ RET + isnull (researchpapertitle, '') +'' + isnull (author, '') +'' + isnull (summary ,'') + ''+ isnull (periodicalname,'') +''
+ Isnull (publishcompany, '') +'' + isnull (periodtime, '') +'' + isnull (content, '') + ';' from xwcmresearchpaper
Where researchpaperid in (select researchpaperid from xwcmresearchpaperuser
Where objid = @ expertid and objtype = 2045598757
) And researchpapertype = 1

If (@ RET is null)
Return''
Return @ RET
End
Go

-- 7. all expert information
-- drop function f_getexpertallinfo
Create Function f_getexpertallinfo (
@ expertid numeric (9)
Returns varchar (8000)
as
begin
declare @ RET varchar (8000)
select @ ret = ''
select @ ret = @ RET + isnull (expertname,'') + '|' + isnull (expertdomain ,'') + '|'
+ DBO. f_getworkhistory (@ expertid) + '|' + DBO. f_geteduhistory (@ expertid) + '|'
+ DBO. f_getprojecthistory (@ expertid) + '|' + DBO. f_getaward (@ expertid) + '|'
+ DBO. f_getresearchpaper (@ expertid) + '|' + DBO. f_getresearchpapermonograph (@ expertid)
from xwcmexpert where expertid = @ expertid
return @ RET
end
go

-- 8. create a view
-- drop view v_coworkplatformexico
Create view v_coworkplatformexico
as
select *, DBO. f_getworkhistory (xwcmexpert. expertid) as workhistory, DBO. f_geteduhistory (xwcmexpert. expertid) as eduhistory,
DBO. f_getprojecthistory (xwcmexpert. expertid) as projecthistory, DBO. f_getaward (xwcmexpert. expertid) as award,
DBO. f_getresearchpaper (xwcmexpert. expertid) as researchpaper, DBO. f_getresearchpapermonograph (xwcmexpert. expertid) as researchpapermonograph,
DBO. f_getexpertallinfo (xwcmexpert. expertid) as expertallinfo
from xwcmexpert