Solved

Stored Procedure with a complex parameetr

Posted on 2007-03-29
17
145 Views
Last Modified: 2010-04-23
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
0
Comment
Question by:balabommala
  • 6
  • 5
  • 5
  • +1
17 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18819056
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18819062
Let's try that again:
Set @YourParameter = @YourParameter + ','           -- Append a comma

Select *
From YourTableName
Where CHARINDEX(YourColumnName + ',', @YourParameter) > 0
0
 

Author Comment

by:balabommala
ID: 18819431
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18819769
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
 
LVL 11

Accepted Solution

by:
dready earned 250 total points
ID: 18820237
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18820264
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18820288
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
 
LVL 11

Expert Comment

by:dready
ID: 18820291
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18820363
:)
0
 
LVL 11

Expert Comment

by:dready
ID: 18820421
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18820521
>>Very funny, same solution in same minute<<
Let's hope the questioner does not use this method.
0
 
LVL 11

Expert Comment

by:dready
ID: 18821097
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
 

Author Comment

by:balabommala
ID: 18823521
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
 
LVL 11

Expert Comment

by:dready
ID: 18823721
Sorry, still not clear to me. Could you show the table structure (which columns, type) and a few example records?
0
 

Author Comment

by:balabommala
ID: 18823788
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
 

Author Comment

by:balabommala
ID: 18836610
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
 

Author Comment

by:balabommala
ID: 18836968
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

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question