Contents
How do I fix subquery returned more than one value?
Try each of the following steps until the issue is resolved.
- If using a SQL job then launch the SQL Server Profiler to trace the exact location of the issue.
- Insert a TOP 1 condition on your SELECT statement (ex: SELECT top 1 max(sequence) FROM TableName WHERE sequence_id=MemberShip.id )
When subqueries are used in a Where statement they can only return one value?
SELECT statement
When subqueries are used in a SELECT statement they can only return one value. This should make sense, simply selecting a column returns one value for a row, and we need to follow the same pattern. In general, the subquery is run only once for the entire query, and its result reused.
What is scalar subquery in SQL?
A scalar subquery is a subquery that selects only one column or expression and returns one row. A scalar subquery can be used anywhere in an SQL query that a column or expression can be used.
Can a subquery return more than one value?
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. [SQLSTATE 21000] (Error 512). The step failed.
When to set a variable to the result of a subquery?
WHERE p. [ProductID] = @ProductID ) Setting a variable to the result of a subquery is one of those cases where the subquery can only have one row so we need to continue on. Now that I have found a possible culprit I convert it somewhat to check for possible duplicate rows.
When does SQL Server return more than one value?
Especially when you are assigning value for a declared variable. The SP never gives you en error but only an runtime error. “System.Data.SqlClient.SqlException (0x80131904): Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Correlated subqueries are essentially cursors as they cause the query to run row-by-row and should be avoided.