Stored Procedure with a complex parameetr

Hi,
I am using SQL Server 2000 and VS.NET 2005.
I need to write a stored procedure that accepts a parameter, say prmName, that can have a value like "Marks, Jones, Bondini". Basically the value is separated by commas.
I need to query the database for any possible combination of those comma separated values.
How can I do this?

Thanks
balabommalaAsked:
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.

Anthony PerkinsCommented:
For the cheesy solution (there are more efficient ways of doing this):

Set @YourParameter = @YourParameter + ','           -- Append a comma

Select *
From YourTableName
Where CHARINDEX(YourColumnName + ',', @YourParameter)
0
Anthony PerkinsCommented:
Let's try that again:
Set @YourParameter = @YourParameter + ','           -- Append a comma

Select *
From YourTableName
Where CHARINDEX(YourColumnName + ',', @YourParameter) > 0
0
balabommalaAuthor Commented:
Thanks for your quick reply.
Let me rephrase my question:
Let's say I have "Marks, Jones" as a value for parameter. I need to check if "Jones,Marks" is present in the database or not. "Marks" alone may be present in the database but I am least bothered about that value.
How can I do this?

Thanks.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

LowfatspreadCommented:
are you wanting to do some sort of free text search ?

have you considered the CONTAINS phrase ...

e.g.

SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, ' "Jones" AND "Marks" ')
0
dreadyCommented:
If i understand i correctly, the inputparam can contain a list of n names, comma separated. (n>=1). And you want to see if any of these names are in your table? So you'd need a function like split, to split the input variable into the different single names.
A function that can do that is:
create function dbo.UTILfn_Split(
 @String nvarchar (4000),
 @Delimiter nvarchar (10)
 )
returns @ValueTable table ([Value] nvarchar(4000))
begin
 declare @NextString nvarchar(4000)
 declare @Pos int
 declare @NextPos int
 declare @CommaCheck nvarchar(1)
 
 --Initialize
 set @NextString = ''
 set @CommaCheck = right(@String,1)
 
 --Check for trailing Comma, if not exists, INSERT
 --if (@CommaCheck <> @Delimiter )
 set @String = @String + @Delimiter
 
 --Get position of first Comma
 set @Pos = charindex(@Delimiter,@String)
 set @NextPos = 1
 
 --Loop while there is still a comma in the String of levels
 while (@pos <>  0)  
 begin
  set @NextString = substring(@String,1,@Pos - 1)
 
  insert into @ValueTable ( [Value]) Values (@NextString)
 
  set @String = substring(@String,@pos +1,len(@String))
 
  set @NextPos = @Pos
  set @pos  = charindex(@Delimiter,@String)
 end
 
 return
end
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
Anthony PerkinsCommented:
>>I need to check if "Jones,Marks" is present in the database or not.<<
Can you define "present in the database"?  Do you mean both "Jones" and "Marks" have to exist at the same time and in any order in a specific column in a specific table?

If the answer is yes, than I would go with Lowfatspread solution.  Just understand that there is some setup and maintenance required when using Full-Text Search.  So please do not try and execute any query with CONTAINS until you have done your homework.
0
Anthony PerkinsCommented:
One other approach I would strongly urge you to resist (even though it is trivial to implement) and that is:

Declare @SQL varchar(8000)
Set @SQL = 'SELECT * FROM YourTableName WHERE YourColumnName In(' + @YourParamValues + ')'
Exec (@SQL)

0
dreadyCommented:
Oh, i have another Idea, but i'm still not 100% sure what you want to accomplish. If you want all the records returned where the name is one of the values in the list, you could do this:

declare @mySQLString as varchar(500)
set @mySQL = 'select * from myTable where name in (' + @myInputParam + ')'
EXEC sp_executesql @mySQLString
0
Anthony PerkinsCommented:
:)
0
dreadyCommented:
Very funny, same solution in same minute (-:
Now it depends on wether the table is called myTable or yourTableName, hehe.

(Eventhough there is a type in mine, should have been:
declare @mySQLString as varchar(500)
set @mySQLString = 'select * from myTable where name in (' + @myInputParam + ')'
EXEC sp_executesql @mySQLString
0
Anthony PerkinsCommented:
>>Very funny, same solution in same minute<<
Let's hope the questioner does not use this method.
0
dreadyCommented:
Just realised it wont work, cause name is a varchar and the values have to be in quotes.

So something like this has to be done:

declare @mySQLString as varchar(500)
set @mySQLString = 'select * from myTable where name in (''' + Replace(@myInputParam, ',', '''') + ''')'
EXEC sp_executesql @mySQLString
0
balabommalaAuthor Commented:
Thanks all for your quick replies.
Here is my problem:
My inputparam can contain a list of n names, comma separated. I have to check the database only for n possible values (not all of them). Lets say I have "Marks, Jones" as input parameter. I have to chcek for 2 possible values in the database "Marks, Jones" and also for "Jones, Marks".
It kind of worked with charindex but if I am searching for "Marks,Jones", it is not able to show "Jones, Marks".
Hope you understood what I want.

Thanks for being conducive.
0
dreadyCommented:
Sorry, still not clear to me. Could you show the table structure (which columns, type) and a few example records?
0
balabommalaAuthor Commented:
Here it is:
I have a table "Names" and a column "LastName":
Here are the values in the table "Names"

Marks
Jones
Marks,Jones
Marks,Jones, Bondini
Jones,Bondini

If I try to input "Jones,Marks" into the table, it mustn't take because "Marks,Jones" is already resent in the table.
Same is the case for other comma separated values. If I try to enter "Bondini,Jones", it must prevent me from entering because "Jones,Bondini"  is already present in the table.
Hope it is clear.

Thanks
0
balabommalaAuthor Commented:
dready's function is close to what I need but 50% more work has to be done.
After separating the values, I need to check if any combination of those in the temp table.
How can I do this?

Thanks
0
balabommalaAuthor Commented:
Let's try that again:
dready's function is close to what I need but 50% more work has to be done.
After separating the values, I need to check if any combination of those in the temp table match with the value in "Names" table.
How can I do this?

Thanks
0
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
Visual Basic.NET

From novice to tech pro — start learning today.

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.