InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 11:06:54 GMT20http://www.sqlservercentral.com/Forums/Topic457395-338-1.aspx[quote][b]Joe90-646727 (2/19/2008)[/b][hr]Hi there,this seems like it should be simple, but is a little frustrating!I have an sp which takes a list of keys like "12,13,14,15,16" as a varchar param.I would like to execute the following query in the sp:select * from tableName where id in (@id_list)I receive the error "Unable to convert between varchar and int".An easy way to reproduce is to run the following codedeclare @id_list varchar(4000)set @id_list='313352,313353'select * from tableName where id in (@id_list)I think dynamic sql might get around it, but I need the sp to be pretty efficient and would prefer not to have to resort to it. Is there a way of doing this without resorting to dynamic SQL? Any help would be great![/quote]Joe,I wrote an article about this type of thing:http://www.sqlservercentral.com/articles/T-SQL/73838/Todd FifieldMon, 22 Aug 2011 10:11:46 GMTtfifieldhttp://www.sqlservercentral.com/Forums/Topic457395-338-1.aspx[quote][b]arshad7887 (8/20/2011)[/b][hr][b]To Run this kind of query first you need to create UDF(user defined function)SPLIT Varchar in sql server User Defined Method[/b] CREATE FUNCTION SplitString ( -- Add the parameters for the function here @myString varchar(500), @deliminator varchar(10))RETURNS @ReturnTable TABLE ( -- Add the column definitions for the TABLE variable here [id] [int] IDENTITY(1,1) NOT NULL, [part] [varchar](50) NULL)ASBEGIN Declare @iSpaces int Declare @part varchar(50) --initialize spaces Select @iSpaces = charindex(@deliminator,@myString,0) While @iSpaces > 0 Begin Select @part = substring(@myString,0,charindex(@deliminator,@myString,0)) Insert Into @ReturnTable(part) Select @part Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0)) Select @iSpaces = charindex(@deliminator,@myString,0) end If len(@myString) > 0 Insert Into @ReturnTable Select @myString RETURN ENDGO[b]Now Run this queryRUN The query[/b]select * From SplitString('Mohammed**Arshad**Shaikh','**')You may use it in where clause also.Declare @Paramlist varchar(50)Set @Paramlist = '1,2,3'SELECT * FROM CustomerWHERE CUSTOMERId In(select * From SplitString(@paramlist,','))This will work sure.Insha Allah..:-)[/quote]No... don't use a While Loop to split strings. They're just too slow. Please see the following article for the code that proves it.[url]http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]Sun, 21 Aug 2011 09:13:17 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic457395-338-1.aspx[b]To Run this kind of query first you need to create UDF(user defined function)SPLIT Varchar in sql server User Defined Method[/b] CREATE FUNCTION SplitString ( -- Add the parameters for the function here @myString varchar(500), @deliminator varchar(10))RETURNS @ReturnTable TABLE ( -- Add the column definitions for the TABLE variable here [id] [int] IDENTITY(1,1) NOT NULL, [part] [varchar](50) NULL)ASBEGIN Declare @iSpaces int Declare @part varchar(50) --initialize spaces Select @iSpaces = charindex(@deliminator,@myString,0) While @iSpaces > 0 Begin Select @part = substring(@myString,0,charindex(@deliminator,@myString,0)) Insert Into @ReturnTable(part) Select @part Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0)) Select @iSpaces = charindex(@deliminator,@myString,0) end If len(@myString) > 0 Insert Into @ReturnTable Select @myString RETURN ENDGO[b]Now Run this queryRUN The query[/b]select * From SplitString('Mohammed**Arshad**Shaikh','**')You may use it in where clause also.Declare @Paramlist varchar(50)Set @Paramlist = '1,2,3'SELECT * FROM CustomerWHERE CUSTOMERId In(select * From SplitString(@paramlist,','))This will work sure.Insha Allah..:-)Sat, 20 Aug 2011 23:55:54 GMTarshad7887http://www.sqlservercentral.com/Forums/Topic457395-338-1.aspx[quote][b]Jeff Moden (3/31/2008)[/b][hr][quote][b]GSquared (3/3/2008)[/b][hr]Just did some tests.The XML version is faster, significantly so, than the Numbers table version, for parsing out a string.[/quote]Any bets? ;) Post your test data please, the Numbers Table code you used for the split, and the XML code you used for the split and we'll see :)[/quote]BWAA-HAA!! Still waiting for this, Gus... :PSat, 21 Feb 2009 12:03:31 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic457395-338-1.aspx[quote][b]GSquared (3/3/2008)[/b][hr]Just did some tests.The XML version is faster, significantly so, than the Numbers table version, for parsing out a string.[/quote]Any bets? ;) Post your test data please, the Numbers Table code you used for the split, and the XML code you used for the split and we'll see :)Mon, 31 Mar 2008 21:39:54 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic457395-338-1.aspxInteresting. Thanks for the info on the while method. :)Wed, 05 Mar 2008 14:27:34 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic457395-338-1.aspxIn the tests I did, the XML method was slightly faster, but required less scans and reads from disk/cache. Speed differences, in many cases, were as few as 1 or 2 milliseconds. But the reduced reads and scans means less I/O bottleneck. May not matter on some systems, but worth it in many cases.(I ran the same tests on a While loop version, and both XML and Numbers versions were consistently at least twice as fast, many times three or more times faster, and in at least one case it was impossible to judge because XML and Numbers ran in less than a millisecond but the While loop took 37 milliseconds. The While loop, on the other hand, also requires less I/O than the Numbers table. If CPU resources are less of a bottleneck on a server than I/O, and XML isn't an option, the While loop [i]might[/i] be viable.)Wed, 05 Mar 2008 11:55:36 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic457395-338-1.aspxGSquared thanks for running the numbers. I know that the XML is the fastet method I have seen, but I had never gotten around to running the numbers.Mon, 03 Mar 2008 12:02:49 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic457395-338-1.aspxJust did some tests.The XML version is faster, significantly so, than the Numbers table version, for parsing out a string.Has the added advantage of being able to take a multi-character delimiter if needed.Of course, it won't work in SQL 2000 (correct me if I'm wrong on that), in which case the Numbers table version is the fastest I've found.Mon, 03 Mar 2008 11:17:23 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic457395-338-1.aspxThanks guys - I don't need ot create a delimited string - the list is coming from use input (selected items in a datagrid).Fri, 22 Feb 2008 07:32:29 GMTJoe90-646727http://www.sqlservercentral.com/Forums/Topic457395-338-1.aspx[quote]Adam Haines (2/19/2008)--------------------------------------------------------------------------------DECLARE @IDString VARCHAR(MAX)SET @IDString =(SELECT (ID) + ','FROM MYTABLE--WHERE FILTER CAN GO HEREFOR XML PATH('')) This leaves a trail comma. I usually do something likeDECLARE @IDString VARCHAR(MAX)SET @IDString =(SELECT CASE row_number() OVER(ORDER BY ID) WHEN 1 THEN '' ELSE ',' END + (ID)FROM MYTABLE--WHERE FILTER CAN GO HEREFOR XML PATH('')) [/quote]Yes, it does leave a trailing character. I typically use a method like the one you posted, but for some reason I did not here :hehe:. I mainly wanted to see if the OP was interested in using XML to generate his delimited string. I did not get a response, so I assume the method he is using is adequate for his use.Thanks for pointing this out Derek. :)Fri, 22 Feb 2008 07:22:01 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic457395-338-1.aspx[quote][b]Adam Haines (2/19/2008)[/b][hr][code]DECLARE @IDString VARCHAR(MAX)SET @IDString =(SELECT (ID) + ','FROM MYTABLE--WHERE FILTER CAN GO HEREFOR XML PATH('')) [/code][/quote]This leaves a trail comma. I usually do something like[code]DECLARE @IDString VARCHAR(MAX)SET @IDString =(SELECT CASE row_number() OVER(ORDER BY ID) WHEN 1 THEN '' ELSE ',' END + (ID)FROM MYTABLE--WHERE FILTER CAN GO HEREFOR XML PATH('')) [/code]Fri, 22 Feb 2008 06:17:27 GMTDerek Dongrayhttp://www.sqlservercentral.com/Forums/Topic457395-338-1.aspxAlso refer http://www.sommarskog.se/arrays-in-sql.htmlThu, 21 Feb 2008 07:03:09 GMTMadhivanan-208264http://www.sqlservercentral.com/Forums/Topic457395-338-1.aspx[quote]SELECT x.i.value('.', 'VARCHAR(7)') as IDto SELECT x.i.value('.', 'int') as IDif your IDs are indeed ints and not text. [/quote]Yes, I created this generically without knowing your data. You should change this to accommodate your data.[quote]select * from (SELECT x.i.value('.', 'VARCHAR(7)') as ID FROM @x.nodes('//i') x(i)) b[/quote]:hehe: I guess I did forget to alias the column. Sorry about that.Wed, 20 Feb 2008 07:24:47 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic457395-338-1.aspxchange SELECT x.i.value('.', 'VARCHAR(7)') as IDto SELECT x.i.value('.', 'int') as IDif your IDs are indeed ints and not text.Wed, 20 Feb 2008 07:03:48 GMTantonio.collinshttp://www.sqlservercentral.com/Forums/Topic457395-338-1.aspxHi guys,thanks a million - I checked out both methods and they both work great. I decided to go with the xml method as it means that I don't need to create ufn's or user tables in the db.By the way in the xml method I had to assign an alias for the returned column i.e.[code]select * from (SELECT x.i.value('.', 'VARCHAR(7)') FROM @x.nodes('//i') x(i)) bshould readselect * from (SELECT x.i.value('.', 'VARCHAR(7)') as ID FROM @x.nodes('//i') x(i)) b[/code]Thanks for your help.Wed, 20 Feb 2008 06:58:46 GMTJoe90-646727http://www.sqlservercentral.com/Forums/Topic457395-338-1.aspxI would diffently do the join. You can either use the XML I provided or a udf. I would prefer the xml though. I dont know how the performance will differ but XML get parsed extremely fast in 2005.[code]DECLARE @x XMLSET @x = '<i>' + REPLACE( @IDString, ',', '</i><i>') + '</i>'SELECT *FROM MyTable a INNER JOIN (SELECT x.i.value('.', 'VARCHAR(7)') FROM @x.nodes('//i') x(i)) bON a.ID = b.ID[/code]Additionally, I dont know how you create your delmited string but XML can also do this very, very quickly. See below:[code]DECLARE @IDString VARCHAR(MAX)SET @IDString =(SELECT (ID) + ','FROM MYTABLE--WHERE FILTER CAN GO HEREFOR XML PATH('')) [/code]Tue, 19 Feb 2008 10:42:22 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic457395-338-1.aspxlook at the post from GSquared regarding the StringParser in this thread: [url=http://www.sqlservercentral.com/Forums/Topic439094-338-2.aspx]http://www.sqlservercentral.com/Forums/Topic439094-338-2.aspx[/url]with it your statement becomes:select * from table join dbo.stringparser(@id_list, ',') as liston table.id = list.parsedor select * from table where id in (select parsed from dbo.stringparser(@id_list, ','))Tue, 19 Feb 2008 09:09:04 GMTantonio.collinshttp://www.sqlservercentral.com/Forums/Topic457395-338-1.aspxyou could use xmlDECLARE @x XMLSET @x = '<i>' + REPLACE( @filter, ',', '</i><i>') + '</i>'SELECT *FROM MyTableWHERE [Status] IN (SELECT x.i.value('.', 'VARCHAR(7)') FROM @x.nodes('//i') x(i))edited to fix xml tagsTue, 19 Feb 2008 08:06:50 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic457395-338-1.aspxHI,It's because your ID column is an Interger and you are comparing it to a string of value, '313352,313353'Hope this helps.ThanksChrisTue, 19 Feb 2008 08:05:56 GMTChristopher Stobbshttp://www.sqlservercentral.com/Forums/Topic457395-338-1.aspxHi there,this seems like it should be simple, but is a little frustrating!I have an sp which takes a list of keys like "12,13,14,15,16" as a varchar param.I would like to execute the following query in the sp:select * from tableName where id in (@id_list)I receive the error "Unable to convert between varchar and int".An easy way to reproduce is to run the following codedeclare @id_list varchar(4000)set @id_list='313352,313353'select * from tableName where id in (@id_list)I think dynamic sql might get around it, but I need the sp to be pretty efficient and would prefer not to have to resort to it. Is there a way of doing this without resorting to dynamic SQL? Any help would be great!Tue, 19 Feb 2008 07:48:06 GMTJoe90-646727