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:

Hi..
I created funtion inside stored procedure and while executing proc,iam getting error as "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

This is my fuction,

SQL

create function FunctionDetails()
returns nvarchar
as
begin
return ( select first_name + last_name as 'Name' from GeneralDetails where first_name like '%b%');
end

And this is my procedure,

SQL

create procedure selectDetailsUsingFunction
(
@id nvarchar
)
as
begin
select @id =dbo.FunctionDetails()
return @id
end

As am new to sql,guide me what iam wrong in this...

Posted 17-Dec-12 23:11pm

Priyaaammu

Add a Solution

Comments

[no name] 18-Dec-12 5:21am

Here your stored procedure asks for ID but your function returns name value. Please be sure what you need to do here. Rectify accordingly...

4 solutions

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)

sisir patro · Answer 1 · 2012-12-17T23:20:00

Hi,

This is absolutely wrong for getting a single record using like operator.
Instead of using the like operator you can use the exact name like:

select ID from GeneralDetails where first_name = 'b'

If you want to retrive the latest record then you can use :

select top 1 ID from GeneralDetails where first_name like '%b%' order by first_name desc

or

<pre> select top 1 ID from GeneralDetails where first_name like '%b%' order by first_name desc</pre>

These are two different methods you can get the result.

Thanks

RelicV · Answer 2 · 2012-12-17T23:25:00

Hi Priya,

In your function, there is a select query with a Where clause. It must be returning multiple records based on the search and where conditions.

Try to implement like this:

SQL

select TOP 1 first_name + last_name as 'Name' from GeneralDetails where first_name like '%b%'

It should resolve the error, but not exactly the answer that you need here. The where condition (also generally called as 'Clause') is not limiting the search to only 1 record here, instead fetching all the records which has 'b' in the first_name column.

Say if the letter 'b' exists in 2 or more records, the search query returns a table but the function can only returns a varchar.

Hope you understood the issue.

Have fun with coding...!

Regards,
Vamsi

Nikhil Khandelwal (MCTS, Web 4.0) · Answer 3 · 2012-12-18T00:03:00

As you are trying to get list of names from Table GeneralDetails. And your Sql functions type is Scaler-valued Function which is always return single value not list of values.

to achieve the same try to use this code ::
In UserDefined Function

SQL

CREATE function FunctionDetails()
returns @returnTable Table ( FullName varchar(50) )
as
begin
INSERT INTO @returnTable 
		Select FirstName + ' ' + LastName from SalesLT.Customer Where FirstName LIKE '%b%'
RETURN		
end

And In Stored Procedure

SQL

select * from dbo.FunctionDetails()

pranathis012 · Answer 4 · 2012-12-18T00:13:00

SQL

select first_name + last_name as 'Name' from GeneralDetails where first_name like '%b%'

this line returns more than once in your query . instead of write "like" in where condition better you can write like this.

SQL

select first_name + last_name as 'Name' from GeneralDetails where first_name = 'b'

what are the test your searching you mentioned in the where condition .

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

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0