July 7, 2014 at 6:39 pm
dbo.Split is not a built in function. They're referencing building a function to do that, such as the following:
http://www.sqlservercentral.com/articles/Tally+Table/72993/
What you basically do is turn the comma delimited string into a temp table, and join against it as your IN clause. I typically write these as:
SELECT
fieldlist
FROM
table
JOIN
splitter(@parameter) AS s
ON table.field = s.field
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 7, 2014 at 6:55 pm
Thank you so much for pointing it out! Craig
I'll try and let you know!
BTW I like your motto too!
July 8, 2014 at 4:22 pm
For everyone else's info.
Here's my whole solution:
CREATE FUNCTION [dbo].[FnSplit]
(@List nvarchar(2000),@SplitOn nvarchar(5))
RETURNS @RtnValue table
(Id int identity(1,1),Value nvarchar(100))
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END
GO
Select city
from Table
where ctiyID in (select value from dbo.Fnsplit(@CityId,','))
July 8, 2014 at 4:44 pm
I assume that you didn't read the article proposed by Craig. Read it and find out a way to improve the performance.
July 8, 2014 at 5:17 pm
Thank you for the reminder Luis!
Will do so now!
Viewing 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply