Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, , >= or when the subquery is used as an expression.

Question

0.00/5 (No votes)

See more:

I have the stored procedure:

USE [authentic]
GO
/****** Object:  StoredProcedure [dbo].[sp_getticket]    Script Date: 11/22/2010 12:11:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_getticket]
@memberid varchar(50)=''
AS
BEGIN
select 
  ticketno, 
  subject, 
  message, 
  attachment  
from 
  tbl_ticket_message 
where 
   messageType='Question' and 
   ticketno=(select ticketno from tbl_ticket where memberid=@memberid)
END

But I get a run time error when it is used by the application:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Posted 21-Nov-10 19:46pm

VinodKumar01

Updated 21-Nov-10 19:59pm

Keith Barrow

v2

Add a Solution

3 solutions

Solution 3

thank you.My english is not good. :)

this code worked my project...

update Coordinates set deleted=@deleted , lastUpdateDate=@lastUpdateDate where  shapeId IN (select DISTINCT  id from deleted);

Posted 3-Mar-14 5:15am

Ramazan Polat

Solution 1

You should read the error message. It

says Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Subquery returned more than 1 value that means this (select ticketno from tbl_ticket where memberid=@memberid) subquery returns more than one value.

This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
It means ticketno=(select ... this expression is not permitted.

Posted 21-Nov-10 20:08pm

Goutam Patra

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Keith Barrow · Accepted Answer · 2010-11-21T20:11:00

This subquery:

select ticketno from tbl_ticket where memberid=@memberid

is returning more than one value. It must return one value for ticketno=(select ticketno from tbl_ticket where memberid=@memberid) to make sense, otherwise which value should SQL use to check for equality?

You have a few options:

Is it allowed for a member to have > 1 ticket number? If not, you have a problem in your DB design that should be fixed as you are allowing multiple entries. You should re-design the table or (if in a fix situation) put a constraint on the table to prevent this, this would also need a data-cleansing exercise on your existing data.
I'm guessing 1 isn't the case. If so, you have two options: The first is to change the subquery so it only returns one value (e.g. add a missing join condition, or select the first/last. The SQL provided in your post will only return the first ticket for the member in the table, is this correct?). The second is to change you condition to
SQL
```
where
   messageType='Question' and
   ticketno <big>IN</big>(select ticketno from tbl_ticket where memberid=@memberid)
```