Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Mng
Yak Posting Veteran
59 Posts |
 Posted - 2013-01-14 : 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 plzDECLARE @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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-14 : 07:31:44
|
Couple of different ways:Change the query to:SELECT * FROM Tbl WHEREEmpnames IN ('John','Deo','Dennis','Haq')If you do want to use a variable rather than string literals, thenSELECT * 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 - 2013-01-14 : 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 WHEREEmpnames IN ('John','Deo','Dennis','Haq')If you do want to use a variable rather than string literals, thenSELECT * 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-14 : 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.EmpNames The 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
52326 Posts |
|
|
|
|
|
|
|
how to check IN clause for strings