How can create a funcation in Sql,But every time where condition changed

Question

5.00/5 (1 vote)

See more:

hi,
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.

SQL

//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

SQL

where  org.Post_date between @Days and @to

for example second

SQL

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 +'%'

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

Posted 21-Dec-13 23:32pm

joginder-banger

Add a Solution

Comments

joginder-banger 22-Dec-13 7:10am

any buddy solve my problem

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jobless Creature · Accepted Answer · 2013-12-22T01:21:00

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..

SQL

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....