Export (0) Print
Expand All
Expand Minimize
3 out of 15 rated this helpful - Rate this topic

SOME | ANY (Transact-SQL)

Updated: 17 July 2006

Compares a scalar value with a single-column set of values. SOME and ANY are equivalent.

Topic link icon Transact-SQL Syntax Conventions


scalar_expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } 
     { SOME | ANY } ( subquery ) 
scalar_expression

Is any valid expression.

{ = | <> | != | > | >= | !> | < | <= | !< }

Is any valid comparison operator.

SOME | ANY

Specifies that a comparison should be made.

subquery

Is a subquery that has a result set of one column. The data type of the column returned must be the same data type as scalar_expression.

SOME requires the scalar_expression to compare positively to at least one value returned by the subquery. For statements that require the scalar_expression to compare positively to every value that is returned by the subquery, see ALL (Transact-SQL). For instance, if the subquery returns values of 2 and 3, scalar_expression = SOME (subquery) would evaluate as TRUE for a scalar_express of 2. If the subquery returns values of 2 and 3, scalar_expression = ALL (subquery) would evaluate as FALSE, because some of the values of the subquery (the value of 3) would not meet the criteria of the expression.

Boolean

SOME or ANY returns TRUE when the comparison specified is TRUE for any pair (scalar_expression, x) where x is a value in the single-column set; otherwise, returns FALSE.

The following example creates a stored procedure that determines whether all the components of a specified SalesOrderID in the AdventureWorks database can be manufactured in the specified number of days. The example uses a subquery to create a list of the number of DaysToManufacture value for all of the components of the specific SalesOrderID, and then tests whether any of the values that are returned by the subquery are greater than the number of days specified. If every value of DaysToManufacture that is returned is less than the number provided, the condition is TRUE and the first message is printed.

USE AdventureWorks ;
GO

CREATE PROCEDURE ManyDaysToComplete @OrderID int, @NumberOfDays int
AS
IF 
@NumberOfDays < SOME
   (
    SELECT DaysToManufacture
    FROM Sales.SalesOrderDetail
    JOIN Production.Product 
    ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID 
    WHERE SalesOrderID = @OrderID
   )
PRINT 'At least one item for this order cannot be manufactured in specified number of days.'
ELSE 
PRINT 'All items for this order can be manufactured in the specified number of days or less.' ;

To test the procedure, execute the procedure by using the SalesOrderID 49080 which has one component that requires 2 days and two components that require 0 days. The first statement below meets the criteria. The second query does not.

EXECUTE DaysToBuild 49080, 2 ;

Here is the result set.

All items for this order can be manufactured in specified number of days or less.

EXECUTE DaysToBuild 49080, 1 ;

Here is the result set.

Some items for this order cannot be manufactured in specified number of days or less.

Release History

17 July 2006

New content:
  • Added the Remarks section.
  • Added the example.
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.