游标值返回 [英] Curosor Value Returning

问题描述

DECLARE @emp_code AS VARCHAR(10)
	Declare @sDate AS DATETIME
	DECLARE @sTime AS DATETIME
	DECLARE @sStatus AS TINYINT
	
	DECLARE @sTimeIn AS DATETIME
	DECLARE @sTimeout AS DATETIME
	DECLARE @TempEmpID AS VARCHAR(10)
	DECLARE @tempsDate AS DATETIME
	
	
	 CREATE TABLE EmpAttendence_Monthly
	 (
		EmpCode		VARCHAR(10),
		AttendenceDate	DATETIME,
		TimeIn			DATETIME,	
	 	TIMEOUT			DATETIME
	 )
	 
	
	SELECT  @sTimein=''''
	SELECT  @sTimeout='''' 
	Declare Q2 cursor FOR
						 SELECT
						  EmpCode,
						  Attendencedate,
						  AttendenceTime,
						  Attendence_Status
						 FROM 
							Emp_MonthlyAttendence 
							INNER JOIN 
				OPEN Q2
				FETCH NEXT FROM Q2
				INTO @emp_code,@sDate,@sTime,@sStatus			
				WHILE @@FETCH_STATUS = 0
					BEGIN
					 ----------------------  Sub Query   ---------------------------------------
						Begin Try
----							
						IF @sStatus =1 AND @sTimein=''''
						BEGIN
								Select @TempEmpID =@emp_code
								Select @tempsDate=@sDate
								select @sTimein= @sTime
						END
						ELSE IF  @sTimeout ='''' AND @sStatus =0	
						BEGIN									
								select @sTimeout= @sTime
							--	PRINT CAST ( @sDate AS VARCHAR(50)) +'',''+  CAST ( @sTime AS VARCHAR(50))
						END 
						IF @sTimein <> '''' AND  @sTimeout <>''''
						BEGIN
								PRINT CAST(@TempEmpID AS VARCHAR(50)) + '' , '' + CAST(@tempsDate AS VARCHAR(50)) + '' , '' + CAST(@sTimein AS VARCHAR(50)) + '' , '' + + CAST(@sTimeOut AS VARCHAR(50))
								INSERT INTO  EmpAttendence_Monthly(	EmpCode,AttendenceDate,TimeIn,TIMEOUT)	
								VALUES(@TempEmpID,@tempsDate,@sTimein,@sTimeout)
								SELECT  @sTimein=''''
								SELECT  @sTimeout='''' 
						END
						
						
						
						
						End Try		
						Begin catch
					 	print ''ERROR : '' +  ERROR_MESSAGE() 				
						End Catch
					FETCH NEXT FROM Q2
					INTO @emp_code,@sDate,@sTime,@sStatus
					END
				CLOSE Q2
				DEALLOCATE Q2			
SELECT * FROM EmpAttendence_Monthly
DROP TABLE EmpAttendence_Monthly

推荐答案

好像要创建一个TEMPORARY表，使用光标填充它，然后从中获取数据.

相反，您正在创建表( NOT 临时表，缺少Hash(#)！)，然后从中选择数据并将其删除！您应该使用表变量而不是临时表(性能方面要领先很多！)很多事情都不对.游标也不是建议的性能方面的原因...
我应该使用#temp表还是@表变量 [

Looks like you wanted to create a TEMPORARY table, use cursor to fill it up and then get the data from it.

instead, you are creating table (NOT temporary table, Hash(#) is missing!), selecting data from it and dropping it! You should use table variables instead of temporary tables (performance wise lot ahead!) Lots of things not right... Cursors too are not suggestible performance wise...
Should I use a #temp table or a @table variable[^]

Elaborate on what you need more?

这篇关于游标值返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！