Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

"Select Where In" using a parameter? Expand / Collapse
Author
Message
Posted Friday, February 22, 2008 7:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 20, 2014 12:27 AM
Points: 2,278, Visits: 3,061
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(''))

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 . 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. :)




My blog: http://jahaines.blogspot.com
Post #459098
Posted Friday, February 22, 2008 7:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 24, 2008 3:47 AM
Points: 3, Visits: 8
Thanks guys - I don't need ot create a delimited string - the list is coming from use input (selected items in a datagrid).
Post #459101
Posted Monday, March 3, 2008 11:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
Just 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.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #463309
Posted Monday, March 3, 2008 12:02 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 20, 2014 12:27 AM
Points: 2,278, Visits: 3,061
GSquared 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.



My blog: http://jahaines.blogspot.com
Post #463342
Posted Wednesday, March 5, 2008 11:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
In 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 might be viable.)


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #464663
Posted Wednesday, March 5, 2008 2:27 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 20, 2014 12:27 AM
Points: 2,278, Visits: 3,061
Interesting. Thanks for the info on the while method. :)



My blog: http://jahaines.blogspot.com
Post #464759
Posted Monday, March 31, 2008 9:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 35,370, Visits: 31,911
GSquared (3/3/2008)
Just did some tests.

The XML version is faster, significantly so, than the Numbers table version, for parsing out a string.


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 :)


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #477388
Posted Saturday, February 21, 2009 12:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 35,370, Visits: 31,911
Jeff Moden (3/31/2008)
GSquared (3/3/2008)
Just did some tests.

The XML version is faster, significantly so, than the Numbers table version, for parsing out a string.


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 :)


BWAA-HAA!! Still waiting for this, Gus... :P


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #662075
Posted Saturday, August 20, 2011 11:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, August 20, 2011 11:49 PM
Points: 1, Visits: 0
To Run this kind of query first you need to create UDF(user defined function)

SPLIT Varchar in sql server

User Defined Method


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
)
AS
BEGIN
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
END
GO

Now Run this query
RUN The query

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 Customer
WHERE CUSTOMERId In(select * From SplitString(@paramlist,','))

This will work sure.
Insha Allah..
Post #1162957
Posted Sunday, August 21, 2011 9:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 35,370, Visits: 31,911
arshad7887 (8/20/2011)
To Run this kind of query first you need to create UDF(user defined function)

SPLIT Varchar in sql server

User Defined Method


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
)
AS
BEGIN
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
END
GO

Now Run this query
RUN The query

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 Customer
WHERE CUSTOMERId In(select * From SplitString(@paramlist,','))

This will work sure.
Insha Allah..


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.
http://www.sqlservercentral.com/articles/Tally+Table/72993/


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1163013
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse