查询在用作表达式时包含多个值。

问题描述

我有一张桌子



tblTimeSlotInformation（用户名，EmpName，日期（日期），日，开始时间（时间（7）），结束时间（时间） （7）），TimeSlot，Topic，ClassroomNo）



如果StartingTime在已经存储的StartingTime和EndingTime之间，则无法插入数据。例如：13.30（StartingTime）到14.30（EndingTime）时间存储在数据库中然后用户尝试将13:45（StartingTime）插入到15:45（EndingTIme）但是消息显示这些时间段已经存在



存储过程

I have one Table

tblTimeSlotInformation(Username, EmpName, Date(date), Day, StartingTime(time(7)), EndingTime(time(7)), TimeSlot, Topic, ClassroomNo)

Data can't insert if StartingTime is between StartingTime and EndingTime which are already store. Ex : 13.30(StartingTime) to 14.30(EndingTime) time store in database then user try to insert 13:45(StartingTime) to 15:45(EndingTIme) but message display that "These time slot is already exist"

Stored Procedure

CREATE Proc spStoreTimeSlotDetails        
@Username nvarchar(50),        
@EmpName nvarchar(50),        
@Date date,        
@Day nvarchar(50),        
@ST time(7),        
@ET time(7),        
@TimeSlot nvarchar(50),        
@Topic nvarchar(50),        
@ClassroomNo int         
as          
begin          
--Declare @count int          
Declare @ReturnCode int        
Declare @MinuteDiff int       
DECLARE @i int = 1      
Declare @NewTime time(7)      
Declare @CRN int    
Declare @D date    

set @ReturnCode=0  
set @MinuteDiff=(select DATEDIFF(MINUTE,StartingTime,EndingTime) from tblTimeSlotDetails)      
set @NewTime=(select StartingTime from tblTimeSlotDetails)  
set @CRN=(Select ClassroomNo from tblTimeSlotDetails)    
set @D=(select [Date] from tblTimeSlotDetails)    

WHILE @i <= @MinuteDiff   
BEGIN      
 set @NewTime=(select DATEADD(MINUTE,1,@NewTime) from tblTimeSlotDetails)    
 if(@CRN=@ClassroomNo and @D=@Date and @NewTime=@ST and @ST=)          
 Begin          
  set @ReturnCode=-1   
  break         
 end          
 else          
 Begin          
  set @ReturnCode=1         
 end          
 SET @i = @i + 1      
End      
if(@ReturnCode=1 or @ReturnCode=0)    
begin   
 Insert into tblTimeSlotDetails values(@Username,@EmpName,@Date,@Day,@ST,@ET,@TimeSlot,@Topic,@ClassroomNo)          
end    
select @ReturnCode as ReturnValue   
end 

但每次运行我的Asp.net c＃应用程序错误提示子查询返回的值超过1。当子查询遵循=，！=，<，< =，>，> =或子查询用作表达式


$ b时，不允许这样做$ b如何编写我的存储过程？请编写整个存储过程。我是数据库中的新手。

but every time when I run my Asp.net c# application error prompt "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"

How Should I write my Stored-procedure? Please write whole stored procedure. I am new in database.

推荐答案

set @MinuteDiff=(select DATEDIFF(MINUTE,StartingTime,EndingTime) from tblTimeSlotDetails)
set @NewTime=(select StartingTime from tblTimeSlotDetails)
set @CRN=(Select ClassroomNo from tblTimeSlotDetails)
set @D=(select [Date] from tblTimeSlotDetails)

如果有任何 SELECT 操作返回多个值：那是试图将两个整数放入一个INT，或者同一辆车后面的两个驱动器！



看看您的数据并确定哪个表返回多个值 - 然后使用 SELECT TOP 1 或更好的 WHERE 子句来限制它只有一个值。

if any of the SELECT operations return more than one value: That's trying to put two integers into a single INT, or two drivers behind the wheel of the same car!

Look at your data and work out which table returns multiple values - then either use SELECT TOP 1 or better a WHERE clause to restrict it to a single value.

这篇关于查询在用作表达式时包含多个值。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！