MS SQL

search

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Aug 2005

Posts: 26

Reputation: aish is an unknown quantity at this point

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 
 MS SQL Syntax (Toggle Plain Text) 
CREATE PROCEDURE JobVacancy
(
@JobRole varchar (50)
)
AS
 
SELECT NoOfVacancies,JobRole FROM JobVacancy WHERE JobRole LIKE @JobRole + '%'
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

Posts: 523

Reputation: pty is on a distinguished road

Solved Threads: 37

pty

Offline

Posting Pro

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

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 
 MS SQL Syntax (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
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

Posts: 523

Reputation: pty is on a distinguished road

Solved Threads: 37

pty

Offline

Posting Pro

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

MS SQL Syntax (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

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