Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
magi325
Starting Member
2 Posts |
 Posted - 2006-01-03 : 11:51:49
|
| Hello,I have in my SP the error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." I can't figure out where is the problem in my SP because the error appears only in one test...in the other cases, all is ok.My SP is smth like that:ALTER PROCEDURE dbo.SP1(@name int) AS SELECT '', CASE TABLE1.COLUMN1WHEN 'C' THEN(SELECT ...)WHEN 'D' THEN(SELECT ...)ENDCOLUMN x,COLUMN y,COLUMN zFROM ...WHERE...It's normal that the subqueries return more rows, it's like that also in the tests which don't return this error, so I don't think that this would be the problem.I'm waiting ur opinions. thx a lot |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-01-03 : 12:09:36
|
| Your subqueries logically execute once for each record in the result set, and if they return more than one value how can the server assign multiple values to a single column of a single record in the result set. It can't, and that is why you get the error. Use aggregate or distinct subqueries to ensure only one record is returned, or better yet rewrite your code to eliminate subqueries in your SELECT clause (never a good idea...). |
 |
|
|
magi325
Starting Member
2 Posts |
Posted - 2006-01-04 : 03:01:57
|
| Thx for ur response. In fact, after posting the topic I figured out that the error is caused by the fact that the subqueries return multiple values instead of a single one, as you said. But I have another question : the result of the procedure's execution is depending by a condition I have in the final WHERE clause(of type: TABLE1.COLUMN1 = @value) so the subqueries are the same for each execution of the procedure and they should return each time the same result if I execute them as separate queries. If so, they should return each time multiple values, not just in some cases...As a possible solution I put SELECT top 1 ...pls have a little patiente with me and make me understand :)thank you |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-01-04 : 11:11:04
|
| You can probably eliminate your subqueries altogether, or at least move them to your FROM clause instead of your SELECT clause. Post your code if you need more help. |
|
|
|
|
|
|
|
|
stored procedure error