Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Compares a scalar value with a single-column set of values.
Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.
ALL requires the scalar_expression to compare positively to every value that is returned by the subquery. For instance, if the subquery returns values of 2 and 3, scalar_expression <= ALL (subquery) would evaluate as TRUE for a scalar_expression 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.
For statements that require the scalar_expression to compare positively to only one value that is returned by the subquery, see SOME | ANY (Transact-SQL).
The following example creates a stored procedure that determines whether all the components of a specified SalesOrderID in the AdventureWorks2012 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 confirms that all the DaysToManufacture are within the number of days specified.
USE AdventureWorks2012 ; GO CREATE PROCEDURE DaysToBuild @OrderID int, @NumberOfDays int AS IF @NumberOfDays >= ALL ( SELECT DaysToManufacture FROM Sales.SalesOrderDetail JOIN Production.Product ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID WHERE SalesOrderID = @OrderID ) PRINT 'All items for this order can be manufactured in specified number of days or less.' ELSE PRINT 'Some items for this order cannot be manufactured in specified number of days or less.' ;
To test the procedure, execute the procedure by using the SalesOrderID 49080, which has one component requiring 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.