Solved

Stored Procedure with a complex parameetr

Posted on 2007-03-29
17
144 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now