April 8, 2010 at 11:13 am
Basically I use a variable table to parse in a csv so that groups of ids can be sent in. I have always used this on a small scale but now it seems developers like it and are using it a lot. Is that ok...Less hits on the server so it is optimized but is there a better way? I cant seem to find anything better.
@GroupList NVarchar(Max)
AS
BEGIN
SET NOCOUNT ON;
SELECT blah from tableA
WHERE BlahId in (SELECT id FROM [dbo].[ListAsTable] ( @GroupList ,','))
END
April 8, 2010 at 12:59 pm
April 8, 2010 at 2:52 pm
I would look at the query plans and see what it is doing. I don't think we have enough information to make a particularly well informed decision.
I have used this method in the past as well, I did create a TVF to handle it so I didn't have the same logic all over the place. I make it flexible by allowing the user to pass in the seperator. Sometimes I had a pipe seperated list that I wanted to be able to handle..
If you are working with LARGE lists you might consider a SQLCLR TVF function, there are a number of cases where text manipulation in CLR is HANDS DOWN faster than SQL. Gert Drapers gave a demonstration that sold me.
CEWII
April 9, 2010 at 7:22 am
Here is the SQL function. Using a CLR can I improve this? The lists that we send in are usually guids. Most our system runs on them. Also we sometimes send 2-5 at a time and other times 30.
DECLARE @Item NVarchar(Max)
DECLARE @Pos int -- Current Starting Position
, @NextPos int -- position of next delimiter
, @LenInput int -- length of input
, @LenNext int -- length of next item
, @DelimLen int -- length of the delimiter
SET @Pos = 1
SET @DelimLen = LEN(@Delimiter) -- usually 1
SET @LenInput = LEN(@sInputList)
SET @NextPos = CharIndex(@Delimiter, @sInputList, 1)
-- Doesn't work for space as a delimiter
IF @Delimiter = ' ' BEGIN
INSERT INTO @List
SELECT 'ERROR: Blank is not a valid delimiter'
RETURN
END
-- loop over the input, until the last delimiter.
While @Pos <= @LenInput and @NextPos > 0
BEGIN
IF @NextPos > @Pos
BEGIN -- another delimiter found
SET @LenNext = @NextPos - @Pos
SET @Item = LTrim(RTrim(substring(@sInputList, @Pos, @LenNext)))
IF LEN(@Item) > 0
Insert Into @List Select @Item
END
-- Position over the next item
SET @Pos = @NextPos + @DelimLen
SET @NextPos = CharIndex(@Delimiter, @sInputList, @Pos)
END
-- Now there might be one more item left
SET @Item = LTrim(RTrim(SUBSTRING(@sInputList, @Pos, @LenInput-@Pos + 1)))
IF Len(@Item) > 0 -- Put the last item in, if found
INSERT INTO @List SELECT @Item
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply