Need help with charindex

Hello, I have the statement below which is not returning results but it should.

I have narrowed the issue down to this condition

and CHARINDEX ([Status], @Status)>0

In the table the column [Status] can be a, b or c and nothing else... no nulls

When the statement below is executed the user may not care to narrow the search by [Status]

SO, in the absence of filtering by either a, b or c  I need to bring back all [Status].

Hope that makes sense?

I exclude the status condition I get my rows.

Am I using CHARINDEX wrong?

Declare @SurveyNumber as varchar(50) set @SurveyNumber='Survey - 101062'
Declare @Field as varchar(150) set @Field =''
Declare @RiskRank as varchar(2) set @RiskRank=''
Declare @Status as varchar(50)set @Status=''
Declare @Scaffolding as bit set @Scaffolding=''
Declare @Priority as varchar(3) set @Priority = ''
Declare @LocationBlockNumber as varchar(50) set @LocationBlockNumber=''

if @Field = ''
set @Field = null

if @RiskRank = ''
set @RiskRank = null

if @Status = ''
set @Status = 0

if @Scaffolding = ''
set @Scaffolding = null

if @Priority = ''
set @Priority = null

if @LocationBlockNumber = ''
set @LocationBlockNumber = null

Select SurveyDetailId From SurveyDetails
where SurveyNumber = @SurveyNumber
And (rtrim(@Field)  is NUll or rtrim(Field)  like rtrim(@Field) + '%')
And (rtrim(@RiskRank)  is NUll or rtrim([RiskRank])  like rtrim(@RiskRank) + '%')
And (rtrim(@Scaffolding)  is NUll or rtrim(Scaffolding)  like rtrim(@Scaffolding) + '%')
And (rtrim(@Priority)  is NUll or rtrim(Priority)  like rtrim(@Priority) + '%')
And (rtrim(@LocationBlockNumber)  is NUll or rtrim(LocationBlockNumber)  like rtrim(@LocationBlockNumber) + '%')
and CHARINDEX ([Status], @Status)>0
Order by Field,Area
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>SO, in the absence of filtering by either a, b or c  I need to bring back all [Status].
This would be typical:  @status=NULL means return all, @status=something means filter on something
WHERE (status = @status OR @status IS NULL)

Open in new window

gogetsomeAuthor Commented:
My problem I think is that there is no way the value of [Status] can be null. It is either a, b or c

All rows added to the table start off with a status of c and as the condition improves it moves to b or a.

When the user is searching for details and chooses not to include [status] as a search condition he wants all [status] to be returned. Hope that helps?
Scott PletcherSenior DBACommented:
Since in this case you're explicitly setting status to 0 if it's not specified, you can do this:

and (@status = '0' OR CHARINDEX ([Status], @Status)>0)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gogetsomeAuthor Commented:
Thank you Scott!

Sorry Jim and thank you for your repeated help these past few days!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.