[AccessD] Stored Procedure SQL IN Clause

I did a piece recently for Builder.com's SQL Tips thing on exactly this.
The trick is to disassemble the string and create a temp table
containing the fragments (one row per frag) then join this to the
relevant query. That way you get 11 rows (say) in your temp table joined
to 1000 rows in your query and performance is lightning fast.

In() sucks because it's incredibly hard to optimize, even by the
smartest optimizers out there. By creating a temp table you get way more
performance. The cost of parsing the string and creating the temp table
is WAY less than doing a table scan, assuming that the table is large.
On 100 rows, who cares? On 1000 or 100K rows, the difference is
dramatic!

Arthur

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robert L.
Stewart
Sent: Monday, February 16, 2004 8:16 AM
To: accessd at databaseadvisors.com
Cc: dba-sqlserver at databaseadvisors.com
Subject: [AccessD] Stored Procedure SQL IN Clause


All,

The end of last week, I was working on a stored procedure in SQL Server.
I 
wanted it to be able to pass in information via a parameter to populate
an 
IN clause in the SQL statement so I could use a multi-select list box
for 
the control on the form.

Here is what I was trying:

I have a list of residence types (house, hotel, mobile home, homeless, 
etc.).  I wanted to be able to select one or more in the list box.

The Stored Procedure looked something like this:

CREATE PROCEDURE uspResidenceTypeList
@InClause Varchar(200)
AS
SELECT *
FROM tblClient
WHERE (ResidenceType IN(@InClause)

This works for a single value, i.e. 'Homeless', but it returns no
records 
when I pass in multiple values.

Does someone have any ideas about what I can do to get this to work?

Thanks,

Robert

Note Cross posted to dba-sqlserver at databaseadvisors.com


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com