[thelist] Using a comma list in an SP- was: Ordering a recordset by an array

Eric Engelmann - Lists eric.lists at geonetric.com
Wed Oct 15 14:08:15 CDT 2003


Snagged this from another list when this question came up, credit to a Mr.
Bob Barrows. I kept it ever since, hope you find it useful.

<snip>
This is a FAQ.

The In() construct expects a list, either a list of literal values separated
by commas, or a list of variables, separated by commas. The query engine
_will not_ parse a variable passed at this point to see if it contains a
list of values. One reason: the mere presence of commas in a variable does
not necessarily mean the variable contains a list of values. Don't you have
any character fields containing data that includes commas in your database?
Why should the query engine know in one case that you're attempting to pass
a list of values, and in another, you're passing the actual data (commas and
all) that you want it to find in the column? Remember, at compile time, the
query engine has no idea what that variable will contain. It _can't _ be
expected to know that it has to parse the future contents of the variable
just because it's the only variable appearing in the In() construct.

There are 5 ways to accomplish what you're after:

1) use charindex to search for the values. Assuming @strArgs has a comma-
delimited list of values:
    WHERE CHARINDEX(',' + LTRIM(intPK) + ',', ',' + @strArgs + ',') > 0

    Of course, this will force a table scan, but performance may be
adequate for your needs.

2) dynamic SQL:
Declare @sql varchar(255)
set @sql = 'select * from tblExample where intPK in ('
set @sql = @sql + @strArgs + ')'
exec(@sql)

Of course, this causes the @sql query to be parsed and recompiled every time
you run it, but the resulting impact on performance is not likely to be as
great in SQL7 as it was 6.5. Again - you will need to test both solutions to
see which one better suits your needs.

3) Insert the values into a #temp table and use an inner hoin to limit your
query's output to the values inserted into the #temp table. This may be the
fastest solution. (but don't take my word for it - test for yourself)

--------------------------------------------------------------------------
4. Thanks to Robert Lummert, at comp.databases.ms-sqlserver:
With SQL 2000 and above, you could use xml, too:

  create table tblExample(intPK int)
  insert tblExample values(3)
  insert tblExample values(56)
  insert tblExample values(34)
  insert tblExample values(300)
  insert tblExample values(301)
  go

  create proc [tmp] as begin
    declare @strArgs varchar(200), @hdoc int
    set @strArgs='<r><n v="3"/><n v="56"/><n v="34"/><n v="300"/></r>'

    exec sp_xml_preparedocument @hdoc output, @strArgs

    select * from tblExample
    where intPK in(
      select v from openxml(
        @hDoc, '/r/n',1
      )
        with(v int)
    )

    exec sp_xml_removedocument @hDoc
  end
  go

  exec tmp
  go

  drop procedure tmp
  drop table tblExample
  go
--------------------------------------------------------------------
5. And lastly, thanks to Marc Litchfield (same NG):
With SQL Server 2000 it's pretty easy to write a UDF to parse a comma-
delimited string and return a table variable (this is called a table-valued
function), so you could do something like this:

    SELECT  a.IdentityID
    FROM  tblExample ex
    JOIN  udfSplitInt(@strArgs) a ON a.Element = ex.intPK

Which would return the position of the value of ex.intPK within the
comma-delimited list (@strArgs).  Here's a possible implementation of
udfSplitInt:

[---- Begin SQL ----]
-- Function:    udfSplitInt
-- Description: Returns a table variable from a string containing a
--                  delimited list of integers
-- Author:      Marc Litchfield, 09/05/01
--
CREATE FUNCTION udfSplitInt
(
        @vchList        varchar(8000)      = '',
        @vchDelimiter   varchar(5)         = ','
)
RETURNS @tblList TABLE (
        IdentityID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
        Element int NULL )
AS
BEGIN

DECLARE @intCurIdx         int,
        @intLastIdx        int,
        @intListLen        int,
        @vchValue          varchar(10)

SELECT  @intCurIdx = 1,
        @intLastIdx = 1,
        @intListLen = LEN(@vchList)

WHILE ( @intCurIdx BETWEEN 1 AND @intListLen )
BEGIN
        SELECT  @intCurIdx = CHARINDEX(@vchDelimiter, at vchList, at intLastIdx),
                @intCurIdx = CASE WHEN @intCurIdx = 0
                                  THEN @intListLen + 1 ELSE @intCurIdx END,
                @vchValue  = LEFT(SUBSTRING(@vchList, at intLastIdx,
                                  @intCurIdx - @intLastIdx),10),
                @vchValue  = REPLACE(CASE WHEN ISNUMERIC(@vchValue) = 0
                                          THEN NULL ELSE @vchValue
END,',','')

        INSERT  @tblList ( Element )
        SELECT  @vchValue

        SET @intLastIdx = @intCurIdx + 1
END

RETURN
END
[---- End SQL ----]
</snip>




More information about the thelist mailing list