strip value out of a variable

[ter79]

I have a situation where I need to see if two certain values are being passed by my app developer via the variable that the stored procedure is using and if the values exists I need to create a flag for each one of them to execute the sp differently and remove the value from the variable.

Hope this makes sense.

 

[gmmastros]

Hope this makes sense.

Almost. Can you please explain a little more with an example?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

I would say, it makes partial sense. Without an example in the code, we can provide partial replies

 

[ter79]

Sure... My stored proceure has a parameter titled "Values" and it's a varchar(max)...

My app developer is passing a string of values
Example: DAte, Name, Sex, Gender, Address, Zip

The stored procedure takes that list and creates a select statment with the parameter to return data. Reason for this stored procedure is that the list of values can grow or shrink.

I need to "strip" out in this example Date and then rewrite the select statement to still use date but I need to get a min and max of the date. The creation of the modified stored procedure I can handle. I just don't know how and if it's possible to "strip" out date of the parameter so the rest of the fields can still retrieve the data as needed but with the addition that I need to create.

Hope that clarify things

 

[gmmastros]

Take a look at this:

Declare @Values varchar(max)

Set @Values = '1/1/2010,Barney,M,F,101 Rubble Way,12345'

Select Left(@Values, CharIndex(',', @Values)-1),
       Right(@Values, Len(@Values) - CharIndex(',', @Values))

Copy/paste to a query window and run it. Does that help?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ter79]

That works, however, I can't guarantee that the date field will always be the first one in the list

 

[gmmastros]

So.... you need to look through a comma delimited list of data and determine if something looks like a date. If it looks like a date, you want to extract the data and then remove it from the comma delimited list of values. Is this correct?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ter79]

Actually, I just spoke with the developer and found out that it will be two certain field names that are affected...

 

[gmmastros]

This sounds like a mess to me.

Are you working on search functionality, where you don't know what the user will be searching on? Sounds to me like the developer wants to throw a bunch of willy nilly data at you with the hopes that you'll be able to make sense of it and return the correct results. If this is the case, I would suggest that there are better ways to handle this. A comma delimited list of data is difficult to deal with already, but when the data is all different (representing different columns in the table) this becomes exponentially worse.

I once had a developer working for me that used to love the word "DYNAMIC". Most of the time "Dynamic" and "performance" are complete opposites, like black and white. I had to rough him up a bit and then showed him the door.

If you really are working on a search stored procedure, then I encourage you to create a separate parameter for each piece of data. You can make the parameters optional and then handle that inside the stored procedure. If you'd like to post more details about the "big picture", I'm willing to help you some more. Treating data in a willy nilly way is NOT how it should be handled. You need to handle your data with respect. Without it, everything else is worthless.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

What is the value of a comma delimites list that you can't determine with any certiantity what would be in the list?

Simi