SQL Server query error

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hello all,

This is my query :

SQL

DECLARE @execquery AS NVARCHAR(MAX)

DECLARE @SELECT VARCHAR(MAX) = (SELECT   UserCode + '_EmailAddressInfo' FROM SUser_Master)



SET @execquery =

'SELECT

'+ @SELECT + '.EmailId

FROM '+ @SELECT + '

GROUP BY '+ @SELECT + '.EmailId

'

EXEC(@execquery )

But Error Is Occured

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

[edit]SHOUTING removed - OriginalGriff[/edit]

Posted 15-Nov-14 1:25am

Ankit Patel(AP)

Updated 15-Nov-14 1:34am

OriginalGriff

v2

Add a Solution

Comments

OriginalGriff 15-Nov-14 7:34am

DON'T SHOUT. Using all capitals is considered shouting on the internet, and rude (using all lower case is considered childish). Use proper capitalization if you want to be taken seriously.

2 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)

OriginalGriff · Answer 1 · 2014-11-15T01:36:00

Solution 1

The error is pretty self explanatory. Your SELECT query returns more than one value - and you can't assign more than one value to a single NVARCHAR.
Try this:

SQL

DECLARE @SELECT VARCHAR(MAX) = (SELECT   UserCode + '_EmailAddressInfo' FROM SUser_Master WHERE myColumn='Some Value that only returns a single row')

Posted 15-Nov-14 1:36am

OriginalGriff

Comments

Ankit Patel(AP) 15-Nov-14 7:42am

BUT THIS Query Retun More then One Value

OriginalGriff 15-Nov-14 8:03am

Yes - that is what the error is saying!
And you can't return more than one value and use it in these circumstances.

So why are you trying to retrieve all the email addresses in the table and try to build a an sql command from it?

Pratik Bhuva · Answer 2 · 2014-11-15T02:20:00

Hello Ankit,

SQL

DECLARE @SELECT VARCHAR(MAX) = (SELECT   UserCode + '_EmailAddressInfo' FROM SUser_Master)

Here you are trying to store result of the Query to a variable @SELECT.
But what if this Query returns more than one value? SQL got confused that which value it should store?
that's why error says "SubQuery Returns More Than One Value."

Here You Have some options...
Either you put specific where condition that returns exact one value...
Or Take top 1 like "Select top 1 userCode from SUser_Master".(Last choice for me).
Or Concat the Result (using ',' or '|' or etc.) and store that into your variable.

Hope You have understand the issue and this might help you.