Ad:

MS SQL Discussion Thread
Unsolved
Views: 2626

Nov 11th, 2005

search

Expand Post »

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

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?

Similar Threads

Getting Your Site Submitted to Search Engines in Search Engine Optimization
search-town keeps coming up as my new home page in Web Browsers
Search for Information From the Address Bar in Internet Explorer in Windows tips 'n' tweaks
Quick Search Tips in Windows tips 'n' tweaks
Turn Off Display and Select an Animated Character in Search Companion in Windows XP in Windows tips 'n' tweaks

aish

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

26 posts
since Aug 2005

Permalink

Nov 13th, 2005

Re: search

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

pty

Reputation Points: 64

Solved Threads: 39

Posting Pro

Offline

530 posts
since Oct 2005

Permalink

Nov 13th, 2005

Re: search

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

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:

aish

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

26 posts
since Aug 2005

Permalink

Nov 16th, 2005

Re: search

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

Quote originally posted by aish ...

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

pty

Reputation Points: 64

Solved Threads: 39

Posting Pro

Offline

530 posts
since Oct 2005

Permalink

Nov 19th, 2005

Re: search

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

aish

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

26 posts
since Aug 2005

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: user creation using msde

Next Thread in MS SQL Forum Timeline: database reminders

DaniWeb Message

Cancel Changes

User Name
Password