SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 putting a sequence of nvarchar with "in" command
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sapator
Constraint Violating Yak Guru

Greece
318 Posts

Posted - 10/13/2012 :  14:08:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/14/2012 :  10:45:20  Show Profile  Reply with Quote
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/
Go to Top of Page

sapator
Constraint Violating Yak Guru

Greece
318 Posts

Posted - 10/17/2012 :  22:16:16  Show Profile  Reply with Quote
Hello.I will have a look,thanks.
Go to Top of Page

sapator
Constraint Violating Yak Guru

Greece
318 Posts

Posted - 10/22/2012 :  18:59:09  Show Profile  Reply with Quote
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.

Edited by - sapator on 10/22/2012 18:59:24
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 10/23/2012 :  11:45:41  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17431 Posts

Posted - 10/23/2012 :  20:46:34  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 10/24/2012 :  04:14:28  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/
Go to Top of Page

sapator
Constraint Violating Yak Guru

Greece
318 Posts

Posted - 10/27/2012 :  22:03:32  Show Profile  Reply with Quote
Thanks.This is one huge article, i get headache by just looking at it.I'll see what i can make out.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000