select where searchvalues are in list

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » select where searchvalues are in list

select where searchvalues are in list

Rate Topic

Display Mode

Topic Options

Author

Message

Mike Saunders NL

Posted Sunday, December 23, 2012 6:37 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 1:06 PM
Points: 14, Visits: 68

Hello all,

I am trying to select row from a table where the column contains values from a list. I tried the following but that query gave me an error (Conversion failed when converting the varchar value '1 OR [AttributeID] = 3 OR [AttributeID] = 6 OR [AttributeID] = 2808' to data type int.):

DECLARE @strSearch VARCHAR(100) = '1,3,6,2808'

SELECT * 
FROM [AttributeList] 
WHERE [AttributeID] = REPLACE(@strSearch, ',', ' OR [AttributeID] = ')

The [AttributeID] is an integer type column and i use SQL Server 2012.

Can someone help me with this, and is this the most efficient way to search?

Best christmas wishes
Mike

Post #1399751

GilaMonster

Posted Sunday, December 23, 2012 7:33 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025

Dynamic SQL (watch for SQL injection) or a string splitter function. I recommend the latter. Search this site for 'Delimited8kSplit'

Oh, and as for why your attempt failed, that equates to this:

SELECT *
FROM [AttributeList]
WHERE [AttributeID] = '1 OR [AttributeID] = 3 OR [AttributeID] = 6 OR [AttributeID] = 2808';

ie where the attribute is equal to that long string, which I'm sure you'll agree isn't going to do what you want

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1399754

CELKO

Posted Sunday, December 23, 2012 11:40 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

I am trying to select row from a table where the column contains values from a list.

Where is the DDL, required by minimal Netiquette? This is not a table by definition. Get that book on RDBMS you were supposed to read, and look up “First Normal Form (1NF)”, the foundation of RDBMS. All values in the columns of the rows of a table are scalar values, not lists! If this were a Geography class, you would be the caveman in the back to the class who hold up his and ask “Earth round? NO! Make flat for Ooog!” Wink

“Attribute” is a meta data term and would never be a proper data element name. The ISO-11179 rule do not allow prefixing a data element name with the data type; that was FORTRAN II, 1960's BASIC and T-SQL's simple one pass compiler (@, @@, #, ##). The physical reasons for this syntax have not been true for decades, but the superstition still prevails among the ignorant.

SQL programmers use the table constructors:

SELECT *
FROM Foobar
WHERE floob_nbr
IN (SELECT X.floob_nbr
FROM (VALUES (1),(3),(6),(2808)) AS X);

If you want to generalize this, you can use a parameter list to build the table.

CREATE PROCEDURE Foobar_Picker
(@p1 CHAR(11) = NULL,
@p2 CHAR(11) = NULL,
@p3 CHAR(11) = NULL,
@p4 CHAR(11) = NULL,
@p5 CHAR(11) = NULL)
AS
SELECT *
FROM Foobar
WHERE floob_nbr
IN (SELECT floob_nbr
FROM (VALUES (@p1), (@p2),
(@p3), (@p4),
(@p5)) AS X(floob_nbr)
WHERE floob_nbr IS NOT NULL);

You have fundamental misconceptions and need to get a basic education in RDBMS.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Post #1399763

Mike Saunders NL

Posted Sunday, December 23, 2012 11:56 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 1:06 PM
Points: 14, Visits: 68

Hi GilaMonster en Celko,

Thanks for your reactions. With help of your tips i've solved my puzzle.
@Celko: Thanks again for your lessons in netiquette, still learning...... Smile

Mike

Post #1399764

Jeff Moden

Posted Sunday, December 23, 2012 5:31 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:25 PM
Points: 32,930, Visits: 26,819

GilaMonster (12/23/2012)

Dynamic SQL (watch for SQL injection) or a string splitter function. I recommend the latter. Search this site for 'Delimited8kSplit'

Here's the link for the DelimitedSplit8K splitter.
http://www.sqlservercentral.com/articles/Tally+Table/72993/

If you need something more sophisticated or need to handle something longer than 8K bytes, I strongly recommend a CLR.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1399774

« Prev Topic | Next Topic »

Permissions