|
|
Posted by Erland Sommarskog on 05/19/06 16:03
@sh (spam@spam.com) writes:
> ListID PropertyID MessageBody
> 1 75,62,2,4 erter tdfgs fd
> 2 6,25,75,23 dsfgsdfgsdfg sd
> 3 2,7,36,2 dfsdfgs dfgsdf
> 4 4,73,75,4 s dfgsdfg sfdg
>
> I want to select a recordset containing only properties with a
> PropertyID of 75, I've tried this...
>
> "SELECT * From Tbl_ListIDs WHERE " & Request("PropertyID") & " IN
> PropertyID"
First of all: do never include user-input data directly an SQL string
like that! That's opens for a security risk known as SQL injection.
Always used parameterised commands. This is also good for performance.
And don't use SELECT * in production code!
> But I'm getting a SQL error of incorrect syntax? I appreciate that
> normally the conditions of the IN statement are visa versa but is there
> an easier way to achieve what I'm trying to do above?
There is no easy way, because this is a database design that violates the
first normal form by having a repeating group.
If you have control over the data model, I strongly recommend you change
the table definition, so that the property ids are strored in a subtable:
CREATE TABLE listproperties (
ListID int NOT NULL,
PropertyID int NOT NULL,
CONSTRAINT pk_listprops PRIMARY KEY (ListID, PropertyID))
If you do not have control over the data model, this may be your best
bet:
SELECT ListID, MessageBody
FROM Tbl_ListIDs
WHERE ',' + ltrim(str(@propertyid)) + ',' LIKE ',' + PropertyID + ','
It is not going to perform very well.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|