[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