Subquery returned more than 1 value. This is not permitted when the subquery follows

Question

0.00/5 (No votes)

See more:

i am receiving this error while am running this query i know there is multiple rows for query how i can edit it in proper way.

This is what i have now.

SQL

<code>ALTER PROCEDURE [dbo].[Sendemailtohr] 
	
	AS
BEGIN
Declare @RequestBy varchar(100),
        @EmpName varchar(100),
        @EmailToID varchar(100),
        @EmailToName varchar(100),
        @Message varchar(500),
        @DocumentName varchar(100),
        @toname varchar(100),
        @EmailStatus char(2),
        @AdminName varchar(100), @AdminEmail varchar(100),
        @TrID int 
--*****************************************************************
    --SET @EmailStatus=(Select TrID From TrDocumentRequest Where EmailStatus IS NULL)
    
	DECLARE ADMX CURSOR FOR SELECT Name, EmailID FROM EmployeeMaster WHERE Active = 'Y' AND Department='HR'
		OPEN ADMX
		FETCH ADMX INTO @AdminName, @AdminEmail
		WHILE @@fetch_status = 0
		BEGIN
		      SET @TrID = (Select TrID From TrDocumentRequest Where EmailStatus IS NULL Group By TrID)
              SET @DocumentName=(Select DocumentCode From TrDocumentRequest where TrID=@TrID)
	          SELECT @EmpName=Name FROM EmployeeMaster WHERE EmpID = (SELECT RequestBy FROM TrDocumentRequest WHERE TrID = @TrID)
    
			SET @Message = '<html>'
				SET @Message = '<html><body><p>Hi ' + @EmpName + ',</p><p>Document Delivery Alert - ' + @DocumentName + '</p>'
			SET @Message = @Message + '<p>Please Deliver the Appropriate Document To ' + @toname + '</p>'
			
			SET @Message = @Message + '</body></html>'
			
			INSERT INTO [EmailSend]
			      ([EmailDate],[EmailFromName],[EmailFromID],[EmpID],[EmailToName],[EmailToID],[Subject],
			      [Message],[EmailType],[ReadIn],[ReadOut],[Deleted],[Sent],[RefTrID],[Retry])
			      Select GETDATE(),'Document Delivery Request','aa@aaa.com',@RequestBy,@AdminName,@AdminEmail,
			      'Document Delivery Request',@Message,'DOC','N','N','N','N',@TrID,0
			FETCH ADMX INTO @AdminName, @AdminEmail
		END

		CLOSE ADMX	
		DEALLOCATE ADMX
	Update TrDocumentRequest Set EmailStatus='Y' Where EmailStatus IS NULL
END</code>

I trying to check the EmailStatus if its null i want to insert data to Emailsend table, surely there is multiple rows returning. please help me out.

Posted 28-May-15 0:53am

tastini

Add a Solution

Comments

Advay Pandya 28-May-15 7:17am

If multiple value will return then you need to choose only one value. So, which value do you want to set as @TrID if you find multiple ? You cant choose multiple values in a variable.

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)

F-ES Sitecore · Accepted Answer · 2015-05-28T01:08:00

Solution 1

SQL like this

SQL

SET @DocumentName=(Select DocumentCode From TrDocumentRequest where TrID=@TrID)

SELECT @EmpName=Name FROM EmployeeMaster WHERE EmpID = (SELECT RequestBy FROM TrDocumentRequest WHERE TrID = @TrID)

will fail if the "(select ...)" returns more than one record, ie there is more than one TrDocumentRequest with a TrID matching @TrID. We can't access your data, so go through all the sub-queries to see which ones are returning multiple rows and solve that issue, whatever it is. Maybe you have rogue data you don't know about, maybe you need to "select top 1" instead....whatever will solve your specific issue.

Posted 28-May-15 1:08am

F-ES Sitecore

v2

Comments

tastini 28-May-15 7:13am

SET @TrID = (Select TrID From TrDocumentRequest Where EmailStatus IS NULL Group By TrID)
this line returns multiple rows. but i have to go through all the rows. and finaly i updating the the EmailStatus='Y' , give me suggession please.

F-ES Sitecore 28-May-15 7:17am

It might be simplest to use a CURSOR

https://technet.microsoft.com/en-us/library/aa258896(v=sql.80).aspx

declare the cursor on the "Select TrID From TrDocumentRequest Where ..." command, and use the WHILE loop to go through each result and process that result.

tastini 28-May-15 7:27am

i change to this way...
DECLARE ADMX CURSOR FOR SELECT Name, EmailID FROM EmployeeMaster WHERE Active = 'Y' AND Department='HR'
OPEN ADMX
FETCH ADMX INTO @AdminName, @AdminEmail
WHILE @@fetch_status = 0
BEGIN
DECLARE SND CURSOR FOR Select TrID From TrDocumentRequest Where EmailStatus IS NULL
OPEN SND
FETCH SND INTO @EmailStatus
WHILE @@fetch_status = 0
BEGIN
SET @TrID = (Select TrID From TrDocumentRequest Where EmailStatus IS NULL And Status=2 Group By TrID)
SET @DocumentName=(Select DocumentCode From TrDocumentRequest where TrID=@TrID)
SELECT @EmpName=Name FROM EmployeeMaster WHERE EmpID = (SELECT RequestBy FROM TrDocumentRequest WHERE TrID = @TrID)

now its inserting multiple times. and same giving error.

F-ES Sitecore 28-May-15 7:45am

Something like this...

DECLARE @TrID int

...

DECLARE SND CURSOR FOR Select TrID From TrDocumentRequest Where EmailStatus IS NULL
OPEN SND
FETCH NEXT FROM SND INTO @TrID
WHILE @@fetch_status = 0
BEGIN
-- you have @TrID now so do things like

SET @DocumentName=(Select DocumentCode From TrDocumentRequest where TrID=@TrID)

-- rest of code here

FETCH NEXT FROM SND INTO @TrID
END

_Asif_ · Accepted Answer · 2015-05-28T02:43:00

There are flaws in your approach, Instead of iterating over the HR Admin users you should have iterated over Documents. Let's do a step by step optimization iteration over your code

Correction and first Optimization Iteration

SQL

DECLARE @TBL TABLE
(
	TRID INT,
	DOCUMENT_NAME VARCHAR(100),
	EMP_NAME VARCHAR(100),
	TO_NAME VARCHAR(100),
	MESSAGE_BODY VARCHAR(1024)
)

INSERT INTO @TBL(TRID, DOCUMENT_NAME, EMP_NAME, TO_NAME, MESSAGE_BODY)
Select TDR.TrID, TDR.DocumentCode, EM.NAME, '', '<html><body><p>Hi ' + EM.NAME + ',</p><p>Document Delivery Alert - ' + TDR.DocumentCode + '</p><p>Please Deliver the Appropriate Document To ' + @toname + '</p></body></html>'
From	(
			SELECT DISTINCT TRID
			FROM TrDocumentRequest
			Where EmailStatus IS NULL
		) INNER_TMP_TABLE INNER JOIN TrDocumentRequest TDR ON INNER_TMP_TABLE.TRID = TDR.TRID
		INNER JOIN EmployeeMaster EM ON TDR.RequestBy = EM.EmpID

    
DECLARE ADMX CURSOR FOR SELECT Name, EmailID FROM EmployeeMaster WHERE Active = 'Y' AND Department='HR'
	OPEN ADMX
	FETCH ADMX INTO @AdminName, @AdminEmail
	WHILE @@fetch_status = 0
	BEGIN
		
		INSERT INTO [EmailSend]
		      ([EmailDate],[EmailFromName],[EmailFromID],[EmpID],[EmailToName],[EmailToID],[Subject],
		      [Message],[EmailType],[ReadIn],[ReadOut],[Deleted],[Sent],[RefTrID],[Retry])
		      Select GETDATE(),'Document Delivery Request','aa@aaa.com',@RequestBy,@AdminName,@AdminEmail,
		      'Document Delivery Request',MESAGE_BODY,'DOC','N','N','N','N',TRID,0
		      FROM @TBL
		FETCH ADMX INTO @AdminName, @AdminEmail
	END
 
CLOSE ADMX	
DEALLOCATE ADMX

In production you will always have more Document Requests which have not been processed by your email processing service. Since this is independent of the context (means not depending over users) a better approach would be to hold all candidate document requests in a temp location
and generating message body as well in the same time and do a bulk insert process like shown above

Second Optimization Iteration

If you closely look at the code, you can acknowledge that the whole loop can be eliminated. The loop is simply fetching hr admin users and tagging it to document requests in message queue.

SQL

DECLARE @TBL TABLE
		(
			TRID INT,
			DOCUMENT_NAME VARCHAR(100),
			EMP_NAME VARCHAR(100),
			TO_NAME VARCHAR(100),
			MESSAGE_BODY VARCHAR(1024)
		)

--*****************************************************************
--SET @EmailStatus=(Select TrID From TrDocumentRequest Where EmailStatus IS NULL)
    
    
INSERT INTO @TBL(TRID, DOCUMENT_NAME, EMP_NAME, TO_NAME, MESSAGE_BODY)
Select TDR.TrID, TDR.DocumentCode, EM.NAME, '', '<html><body><p>Hi ' + EM.NAME + ',</p><p>Document Delivery Alert - ' + TDR.DocumentCode + '</p><p>Please Deliver the Appropriate Document To ' + @toname + '</p></body></html>'
From	(
			SELECT DISTINCT TRID
			FROM TrDocumentRequest
			Where EmailStatus IS NULL
		) INNER_TMP_TABLE INNER JOIN TrDocumentRequest TDR ON INNER_TMP_TABLE.TRID = TDR.TRID
		INNER JOIN EmployeeMaster EM ON TDR.RequestBy = EM.EmpID
				
INSERT INTO [EmailSend]
				  ([EmailDate],[EmailFromName],[EmailFromID],[EmpID],[EmailToName],[EmailToID],[Subject],
				  [Message],[EmailType],[ReadIn],[ReadOut],[Deleted],[Sent],[RefTrID],[Retry])
Select GETDATE(),'Document Delivery Request','aa@aaa.com',@RequestBy,Name,EmailID,
'Document Delivery Request',MESSAGE_BODY,'DOC','N','N','N','N',TRID,0
FROM @TBL T CROSS JOIN 
	(SELECT Name, EmailID FROM EmployeeMaster WHERE Active = 'Y' AND Department='HR') A 

		
Update TrDocumentRequest Set EmailStatus='Y' Where EmailStatus IS NULL

I have also noticed that some variables are not initialized appropriately like @RequestBy