Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

select where searchvalues are in list Expand / Collapse
Author
Message
Posted Sunday, December 23, 2012 6:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 6:31 AM
Points: 23, Visits: 92
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
Posted Sunday, December 23, 2012 7:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 40,193, Visits: 36,597
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
Posted Sunday, December 23, 2012 11:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:27 PM
Points: 1,945, Visits: 3,068
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!”

“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
Posted Sunday, December 23, 2012 11:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 6:31 AM
Points: 23, Visits: 92
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......

Mike
Post #1399764
Posted Sunday, December 23, 2012 5:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1399774
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse