Not sure of best way to select different status values

Question

Not sure of best way to select different status values

Viewing 12 posts - 31 through 41 (of 41 total)

You must be logged in to reply to this topic. Login to reply

meichner SSCertifiable Points: 5563 More actions · Answer 1

That is really cool. I never knew that you could flip the Like statement the way you did. Before I implement this I want to be sure that I understand what is going on. Am I correct that you are basically making the database field Status look as though it contains the pipe symbol so that it can match the @StatusCodes field?

Thanks

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

I'm forcing the like to compare something that looks like this (the @statuscodes)

|2|3|4|12|15|

to this (what I'm doing to status)

|1|

Now - I'm forcing the | on either side of the status so I don't end up with "false matches". Matching for %1% for example would have yielded matches against 12 AND 15 (not what we wanted), whereas %|1|% ONLY matches when 1 is in the list as 1, and not as part of another number.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

meichner SSCertifiable Points: 5563 More actions · Answer 3

This was very helpful. Casting the Status field into a VarChar that was surrounded by a delimiter during the selection never occurred to me.

Thanks. I really appreciate your help.

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 4

Lets say your statuses are single character.

Lets also say that your valid statuses are A, B, C, and D

You could pass in a single varchar variable with the ones you are looking for

Declare @ParmList varchar

Set @ParmList = 'AD'

You can then search your table for the appropriate value with

Select * From MyTable

where charindex(StatusColumn,@ParmList) > 0

--might need to switch parameters. Always forget correct order.

This of course won't perform well against large tables, but will get the job done.

meichner SSCertifiable Points: 5563 More actions · Answer 5

I have been getting some interesting solutions to my question. I appreciate them all as I learn something from everyone one of them. Am I correct that your solution won't work if my status values are more then one digit long? My status values (at this point) range from 1-17.

Thanks

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Matt's solution will work perfectly for statuses up to 7998 characters long if stored in a VARCHAR(8000) and much more than that if in a VARCHAR(MAX)...

If you end up with more than, say, 10 or 15 items to parse and compare, it will probably be of some benefit to parse them out to a temporary lookup table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

meichner SSCertifiable Points: 5563 More actions · Answer 7

Maybe I did something incorrectly, but when I executed this code using Set @ParmList = '1', the resultset contained rows with status values 1 and 12. Here is the code that I wrote:

Declare @ParmList varchar(max)

Set @ParmList = '1'

Select * From MyTable Where charindex(@ParmList, Status) > 0

In this case I only wanted to rows that had a status of 1.

When I set ParamList to '13' no rows were returned instead of rows that contained either a 1 or a 3 (those rows do exist).

Thanks

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

That's because you didn't do like Matt did... you must include a delimiter on either side of what you're searching for...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

meichner SSCertifiable Points: 5563 More actions · Answer 9

Matt's method worked perfectly. SSC Journeyman had suggested a parameter list that didn't have delimiters. That is why I didn't include them in my first attempt. Regardless of that I must be doing something else wrong, because adding delimiters didn't help either.

When I executed the code below, No results were returned:

Declare @ParmList varchar(8000)

Set @ParmList = '|1|2|'

Select * From MyTable Where charindex(@ParmList, Status) > 0

When I executed the code below, the correct results were returned:

Declare @ParmList varchar(200)

Set @ParmList = '|1|'

Select * From MyTable Where charindex(@ParmList, '%|' + Cast(ProcessingStatus as Varchar(200)) +'|%' ) > 0

When I executed the code below, No results were returned:

Declare @ParmList varchar(200)

Set @ParmList = '|1|2|'

Select * From MyTable Where charindex(@ParmList, '%|' + Cast(ProcessingStatus as Varchar(200)) +'|%' ) > 0

The only difference between the 2nd and 3rd attempts was the parameter list.

Thanks

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

meichner (2/27/2008)
Matt's method worked perfectly.

Does that mean that you're all set or is there still a problem?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Srdjan Svrdlan SSC Veteran Points: 204 More actions · Answer 11

Hm...we solved that problem by introducing a UDF which returns a table of values, parsing the string of statuses with commas as delimiters, for example.

So you could pass a varchar, or nvarchar parametar to a procedure

EXEC SOME_PROC '1,3,4,5' or EXEC SOME_PROC 'Active, On Hold' or whatever, and inside you would have a query like

SELECT * FROM MyTable WHERE Status IN

(SELECT ST FROM dbo.GET_STATUS(@statuses))

You could even map the passed statuses to int values with a CASE statement

SET @hlp_stat = @statuses

IF CHARINDEX(',', @hlp_stat) > 0

BEGIN

SET @hlp_stat = @hlp_stat + ','

WHILE (CHARINDEX(',',@hlp_stat) >0)

BEGIN

SET @st = LEFT(@hlp_stat, CHARINDEX(',',@hlp_stat)-1)

SET @hlp_stat = SUBSTRING(@hlp_stat, CHARINDEX(',',@hlp_stat)+1, LEN(@hlp_stat))

IF @st <> ''

INSERT INTO @ret_tab (ST) VALUES (@st)

END

ELSE

INSERT INTO @ret_tab (ST) VALUES (@hlp_stat)

RETURN @ret_tab

We didn't have any performance issues so far.

Hope it helps.

-----------

Sorry guys, I jumped to replay to quickly...didn't see that you already posted a couple of solutions...didn't mean to spam or something.

meichner SSCertifiable Points: 5563 More actions · Answer 12

I don't seem to be able to get SSC Journeyman's method to work, but since Matt's works fine I will stick with that.

Thanks for the time and patience.