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?
Scott PletcherConnect With a Mentor Senior 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)
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?
gogetsomeAuthor Commented:
Thank you Scott!

Sorry Jim and thank you for your repeated help these past few days!!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.