putting a sequence of nvarchar with "in" command

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

putting a sequence of nvarchar with "in" command

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2012-10-13 : 14:08:28

Hello.I would like to put a sequence on names "jim,mike,john,..etc" with a @variable inside an "in" command. So something like "and name in(@variable)". I merged a nvarchar variable with the names (so @variable contains "jim,mike,john" but when i use it on an in command it will only work if i only have one name in it.So can this be done simple or i may need dynamic sql?
Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-14 : 10:45:20

Intuitively one would think that what you are describing should work, but unfortunately, SQL Server does not work that way. There are couple of different ways that you can accomplish what you are looking to do. The quick and easy way is shown below, but it is likely to be slow if you have a lot of data:

SELECT col1,col2,...
FROM YourTable
WHERE 
	','+@variable+',' LIKE '%,'+[name]+',%'

The more efficient way, albeit requiring a little bit more work is to split the comma-separated values into a virtual table and then join on that table. You will need a splitter function - many are available on the web. One I particularly like is Jeff Moden's code in Fig. 21 in this article: http://www.sqlservercentral.com/articles/Tally+Table/72993/

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2012-10-17 : 22:16:16

Hello.I will have a look,thanks.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2012-10-22 : 18:59:09

Hi.I'm using your simple solution but since i also wanted a series of bigint's i am doing a cast between nvarchar and bigint.It's dirty but seems to work.Should i worry of something?The bigint's are in the PK so i guess it's safe for overflow.
Thanks.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-10-23 : 11:45:41

look into table valued parameters.

http://www.sommarskog.se/arrays-in-sql-2008.html

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-10-23 : 20:46:34

TVP is not available in SQL 2005 only 2008 onwards.

for SQL 2005 check out this instead http://www.sommarskog.se/arrays-in-sql-2005.html

KH
[spoiler]Time is always against us[/spoiler]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-10-24 : 04:14:28

ah yeah -- sorry missed the forum type.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2012-10-27 : 22:03:32

Thanks.This is one huge article, i get headache by just looking at it.I'll see what i can make out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources