| Author |
 Topic  |
|
|
Mng
Yak Posting Veteran
59 Posts |
 Posted - 01/14/2013 : 07:13:21
|
I have a requirement to check whether passed names exist in table or not ? How to acieve this? Can some one share the query plz
DECLARE @EMPNAMES = 'John, Deo, Dennis, Haq'
Ex: Select * from table where Empnames IN(@EMPNames)
IN clause will work only for integers. The same how can i acheive with strings. |
|
|
James K
Flowing Fount of Yak Knowledge
1483 Posts |
Posted - 01/14/2013 : 07:31:44
|
Couple of different ways:
Change the query to:SELECT * FROM Tbl WHERE
Empnames IN ('John','Deo','Dennis','Haq')
If you do want to use a variable rather than string literals, then
SELECT * FROM Tbl WHERE ','+@EMPNames+',' LIKE '%,'+EmpNames+',%'
While that is quick and easy coding, couple of things to keep in mind:
1. If you have spaces in addition to commas separating the tokens (as you have in the example you posted, you would need to make changes to accommodate that).
2. This would not be able to use any index you may have on EmpNames column.
Another alternative is to split the @EmpNames token into a virtual table and then join with that table. For anything but small tables, I would recommend this second method. If you search SQLTeam or the web, you will find several string splitter functions http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648 |
 |
|
|
Mng
Yak Posting Veteran
59 Posts |
Posted - 01/14/2013 : 07:39:40
|
James,
Thanks for your reply. I will get the input to stored procedure as 'John,Deo,Dennis,Haq'. My query should return the data which contains these names.
I didn't understand the split funcitionality here. And also your 1st option says pass data as 'John','Deo','Dennis'. How can i pass like this if i get the input as 'John,Deo,Dennis,Haq'.
Appreciate it if you can provide the full solution(query).
quote:
Originally posted by James K
Couple of different ways:
Change the query to:SELECT * FROM Tbl WHERE
Empnames IN ('John','Deo','Dennis','Haq')
If you do want to use a variable rather than string literals, then
SELECT * FROM Tbl WHERE ','+@EMPNames+',' LIKE '%,'+EmpNames+',%'
While that is quick and easy coding, couple of things to keep in mind:
1. If you have spaces in addition to commas separating the tokens (as you have in the example you posted, you would need to make changes to accommodate that).
2. This would not be able to use any index you may have on EmpNames column.
Another alternative is to split the @EmpNames token into a virtual table and then join with that table. For anything but small tables, I would recommend this second method. If you search SQLTeam or the web, you will find several string splitter functions http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1483 Posts |
Posted - 01/14/2013 : 08:13:59
|
When the parameter is passed into a stored procedure, you cannot use the first method that I described.
You can use the second method exactly as I had posted earlier.
For the third case, you need to find a splitter function. The example below uses the function that Seventhnight posted as the first reply in the thread referred to in my previous post. Copy his code into an SSMS window and run it to install the function. Then you would use that like shown below:
SELECT
e.*
FROM
Tbl e
INNER JOIN dbo.Split(@EMPNames,',') s
ON s.Data = e.EmpNamesThe function SeventhNight posted uses a while loop to do the splitting. I recall seeing more efficient functions - but don't recall where at the moment; you should be able to find them if you google |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
|
| |
Topic |
|
|
|
how to check IN clause for strings
