如何在Sql中创建一个函数，但每次条件都改变了 [英] How can create a funcation in Sql,But every time where condition changed

问题描述

我不知道怎么办，我工作的网站，这个网站就像求职者。我想搜索许多类型，如工作档案，位置，工资等。这个查询我认为在我的网站上使用了超过一百次。

I have no idea how can do this,I am working web site, this website is like job seeker. I want searching many type like that job profile,location,salary and so on. This Query i think more than hundred times used in my website.

//This query repeat again and again 
select org.SkillsRequired,job.Catagary_Name,cty.City,fa.FuncationalArea,orgdt.OrganisationName,jq.Qualification from tbl_Job_OrgOpeningDetail org 
inner join tbl_job_catagary job on JobCatagary=job.Catagary_id 
inner join Tbl_Job_City cty on JobLocation=cty.City_id
inner join tbl_job_Functional_Area fa on Role_type=fa.Funcational_id
inner join  tbl_job_OrgDetails orgdt on  org.OrgDt_id=orgdt.OrgDt_id
inner join  tbl_job_Qualification jq on MinimumQualification=jq.Qualification_id

但问题是每次条件改变时。例如，首先

but problem is every time where condition changed. for example first

where  org.Post_date between @Days and @to

例如第二个

for example second

where org.JobCatagary   LIKE '%' +@cat+'%'
and org.JobLocation like '%' +@_jobLocationId +'%'
and  org.Role_type like '%'+@Rol +'%'
and org.Org_Salary like '%' +@sal+'%'
and org.MinimumExprinceYear like '%' +@exp +'%'

使用了多种类型的where条件。

我想为什么重复并重复使用上面的查询。怎么能避免这种情况。感谢提前。

任何查询和信息以及任何疑问都会留下评论。我正在等待

so many type of where condition used.
I think why repeat and repeat use above query. How can avoid this. thanks advance.
for any query and information and any doubt plz left the comment. i am waiting

推荐答案

您可以尝试以下方式..



如果未选择特定条件，将它设置为NULL。



例如，



如果未传递@ROl，则显式设置为NULL。然后尝试以下查询..

You can try the following..

If a particular condition is not selected, set it as NULL.

For example,

if @ROl is not passed, explicitly set is as NULL. Then try the following query..

select
org.SkillsRequired,job.Catagary_Name,cty.City,fa.FuncationalArea,orgdt.OrganisationName,jq.Qualification
from tbl_Job_OrgOpeningDetail org
inner join tbl_job_catagary job on JobCatagary=job.Catagary_id
inner join Tbl_Job_City cty on JobLocation=cty.City_id
inner join tbl_job_Functional_Area fa on Role_type=fa.Funcational_id
inner join  tbl_job_OrgDetails orgdt on  org.OrgDt_id=orgdt.OrgDt_id
inner join  tbl_job_Qualification jq on MinimumQualification=jq.Qualification_id
(@_jobLocationId IS NULL OR org.JobLocation like '%' +@_jobLocationId +'%')
and
(@Rol IS NULL OR org.Role_type like '%'+@Rol +'%')
and
(@sal IS NULL OR org.Org_Salary like '%' +@sal+'%')
and
(@exp  IS NULL OR org.MinimumExprinceYear like '%' +@exp +'%')
AND
(@Days IS NULL OR @to IS NULL OR org.Post_date between @Days and @to)

希望这有帮助......

Hope this helps....

这篇关于如何在Sql中创建一个函数，但每次条件都改变了的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！