search

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 402,420 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,041 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MS SQL advertiser: Programming Forums

Thread Tools Display Modes

Views: 1961 | Replies: 4

•

Join Date: Aug 2005

Posts: 26

Reputation: aish is an unknown quantity at this point

Rep Power: 4

Solved Threads: 0

aish

Offline

Light Poster

search

Nov 11th, 2005

I have asp.net C# web application I want to search jobvacancy details using jobrole.
if user did not select any value I want to select all the details including null values.

this Jobvacancy table has JobRole feild and it allow to insert null values,

this is my stored procedure

Help with Code Tags

(Toggle Plain Text)

CREATE PROCEDURE JobVacancy
(
@JobRole varchar (50)
)
as

Select NoOfVacancies,JobRole from JobVacancy where JobRole LIKE @JobRole + '%'

when I use @JobRole value as '%' then if JobRole feild has null value it did not select.
but I want to select all the values including null values. how can I do this?

•

Join Date: Oct 2005

Location: Manchester, UK

Posts: 481

Reputation: pty is on a distinguished road

Rep Power: 3

Solved Threads: 31

pty

Offline

Posting Pro in Training

Re: search

Nov 13th, 2005

select
NoOfVacancies,
JobRole
from JobVacancy
where (JobRole like @JobRole + '%')
or (JobRole is null)

•

Join Date: Aug 2005

Posts: 26

Reputation: aish is an unknown quantity at this point

Rep Power: 4

Solved Threads: 0

aish

Offline

Light Poster

Re: search

Nov 13th, 2005

Thanks for your help using your ideas finally I manage to write a correct query,

Help with Code Tags

(Toggle Plain Text)

CREATE PROCEDURE JobVacancy
(
@JobRole varchar (50)
)
as

if @JobRole <> '%'
begin 
Select NoOfVacancies,JobRole from JobVacancy where JobRole LIKE @JobRole+ '%' 
end
else
begin 
Select NoOfVacancies,JobRole from JobVacancy where (JobRole LIKE @JobRole+ '%' OR JobRole is null)
end

:evil:

•

Join Date: Oct 2005

Location: Manchester, UK

Posts: 481

Reputation: pty is on a distinguished road

Rep Power: 3

Solved Threads: 31

pty

Offline

Posting Pro in Training

Re: search

Nov 16th, 2005

no problem, but with your solution i don't understand why you use an if..else statement here

•

Originally Posted by aish

Help with Code Tags

(Toggle Plain Text)
if @JobRole <> '%'
begin 
Select NoOfVacancies,JobRole from JobVacancy where JobRole LIKE @JobRole+ '%' 
end
else
begin 
Select NoOfVacancies,JobRole from JobVacancy where (JobRole LIKE @JobRole+ '%' OR JobRole is null)
end
if @JobRole <> '%' begin Select NoOfVacancies,JobRole from JobVacancy where JobRole LIKE @JobRole+ '%' end else begin Select NoOfVacancies,JobRole from JobVacancy where (JobRole LIKE @JobRole+ '%' OR JobRole is null) end

im sure you'd get the same result by just saying

select NoOfVacancies, JobRole
from JobVacancy
where JobRole like @JobRole + '%'
or JobRole is null

if @JobRole is '%' your statement would say '..where JobRole like '%%' which is the same as saying where JobRole like '%'; both solutions would work but I think you may be overcomplicating slightly.

on the right track though

•

Join Date: Aug 2005

Posts: 26

Reputation: aish is an unknown quantity at this point

Rep Power: 4

Solved Threads: 0

aish

Offline

Light Poster

Re: search

Nov 19th, 2005

your correct I can get the result with out using if else. but then for ex. if I want to select 'Programmer' then JobRole=='programmer' and JobRole=='null' selected but I want to select JobRole=='null' only when I put JobRole=='%' otherwise I don't want to select null values.
:o