从SQL Server获取查询异常 [英] Getting a query exception from the SQL server

问题描述

当我从Java代码中调用过程时，出现了如下异常:
com.microsoft.sqlserver.jdbc.SQLServerException:子查询返回了多个值.当子查询遵循=，！=，<，< =，>，> =或将子查询用作表达式时，不允许这样做.

步骤如下:

When I call the procedure from the java code I am geting an exception as
com.microsoft.sqlserver.jdbc.SQLServerException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The procedure is as follows:

CREATE PROCEDURE [dbo].[SENDMAIL] @Rollno varchar(11) 

OUTPUT, @mailid varchar(80) OUTPUT, @flag INT OUTPUT
AS
BEGIN
declare @cday varchar(11), @currday varchar(11), 

@currenttime varchar(20), @todaysDate varchar(20)

select @cday= [CHECK_DAY] from Students_Data where 

ROLL_NO=@Rollno;

select @mailid=[CONTACT_MAIL_ID] FROM Students_Data 

where ROLL_NO=@Rollno;

select @currday=DATENAME(DW, GETDATE());

select @todaysDate=convert (varchar(10), getdate(), 

120);

select @flag=1;


IF(@cday = @currday)
BEGIN
  IF((SELECT LEFT(CONVERT(TIME(0),GETDATE()) ,5)) >

(select DEADLINE_TIME from Students_Data WHERE 

ROLL_NO=@Rollno))
  BEGIN
    IF EXISTS (SELECT CRETAED_DATE FROM STUDENT_MAIL 

WHERE ROLL_NO=@Rollno AND CRETAED_DATE=@todaysDate)
	BEGIN
	  SET @flag = '1';
    END
    ELSE
     IF EXISTS (SELECT DISTINCT(ROLL_NO) FROM 

STUD_RECORDS WHERE ROLL_NO=@Rollno AND CRETAED_DATE 
		BETWEEN 
		(SELECT CONVERT(char(10), GetDate()-

7,126))
		AND
		(SELECT CONVERT(char(10), GetDate

(),126)))
		 BEGIN
		 SET @flag = '1';
		 END
		 ELSE
           SET @flag = '0';
           INSERT INTO STUDENT_MAIL(ROLL_NO) VALUES

(@Rollno);
     END
	END
END

推荐答案

Mirza，

我认为以下代码块会导致此错误...

Hi Mirza,

I think following block is causing this error...

IF((SELECT LEFT(CONVERT(TIME(0),GETDATE()) ,5)) >
 
(select DEADLINE_TIME from Students_Data WHERE ROLL_NO=@Rollno))

确保子查询应始终基于 @Rollno
返回单行
如果不可能，请在查询中使用 TOP 子句，如..

Make sure that the sub query should always return a single row based on @Rollno

If that is not possible use the TOP Clause in the query like ..

IF((SELECT LEFT(CONVERT(TIME(0),GETDATE()) ,5)) >
 
(select TOP 1 DEADLINE_TIME from Students_Data WHERE ROLL_NO=@Rollno))

谢谢

Thank you

这篇关于从SQL Server获取查询异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！