Sql Cursron结果只返回单行 [英] Sql Cursron Result Return only single row
本文介绍了Sql Cursron结果只返回单行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
使用游标创建了一个存储过程,并尝试从asp.net调用存储过程想要在gridview中显示问题是它只返回仅第一行的单行。是这样的吗?我需要编辑存储过程的东西。或者在asp代码中?
ALTER Proc [dbo]。[EmpShiftCal]
As
开始
声明 @ ShiftName varchar ( 50 ),
@ CheckTime 时间,
@ WorkDate date ,
@ StartDate date ,
@ EndDate date ,
@EmpID int ,
@ CheckType char ( 8 ),
@ StartTime 时间,
@ AttDate datetime ,
@ TimeNow 时间,
@TimePlus 日期时间,
@ DateConvert datetime ,
@ date1 date , @ date2 date
设置 @ date1 = dateadd(month,-1,GETDATE());
设置 @ date2 = GetDate()
DECLARE EmpShift CURSOR FOR
选择 EmpShift.EmpID,EmpShift.ShiftName,EmpShift.StartDate,EmpShift.EndDate,
转换( varchar ( 10 ),CHECKINOUT.CheckTime, 121 )作为 WorkDate, convert ( char ( 5 ),CHECKINOUT.CheckTime, 108 ) As CheckTime,
CHECKINOUT.CheckType,CHECKINOUT.CheckTime
From EmpShift
内部 Join CHECKINOUT On
EmpShift.EmpID = CHECKINOUT.USERID
其中((EmpShift.StartDate @ date1 AND @ date2 ) AND (EmpShift.EndDate @ date1 AND @ date2 ))和 EmpShift.EmpID = 191
AND ( CHECKINOUT.CheckTime @ Date1 AND < span class =code-sdkkeyword> @ Date2 )
GROUP BY EmpShift.EmpID,转换( varchar ( 10 ),CHECKINOUT.CheckTime, 121 ), convert ( char ( 5 ),CHECKINOUT.CheckTime, 108 ),EmpShift.EmpID,EmpShift.ShiftName,
EmpShift.StartDate,EmpShift .EndDate,CHECKINOUT.CheckType,CHECKINOUT.CheckTime
order by StartDate
< span class =code-keyword>打开 EmpShift
FETCH NEXT FROM EmpShift INTO @EmpID , @ ShiftName , @ StartDate , @ EndDate , @ WorkDate , @ Chec kTime , @ CheckType , @ DateConvert
while (@@ FETCH_STATUS = 0)
BEGIN
选择 @ StartTime = StartTime From ShiftTable 其中 ShiftName = @ ShiftName
if (@ ShiftName!= ' ' 和 @ CheckType = ' 我')
开始
设置 @ TimePlus = DATEADD(DAY, 1 , @ WorkDate )
设置 @ AttDate = datediff(dd, @ WorkDate , @ WorkDate )
设置 @TimeNow =(选择 MAX(CHECKINOUT.CheckTime) From CHECKINOUT 其中 CheckTime @AttDate AND @ TimePlus AND CHECKTYPE = ' O' AND USERID = 191)
选择 @EmpID 作为 EmpId, @ ShiftName 作为名称, @ StartDate 作为 StartDate, @ EndDate 作为 EndDate,
@ WorkDate 作为 WorkDate, @ CheckTime < span class =code-keyword> As CheckTime, @ CheckType As CheckType,< span class =code-sdkkeyword> @ TimeNow As TimeNow, @ AttDate As DateAttendance,
DATEDIFF(N, @ CheckTime , @StartTime )作为 LateInMinutes,
DATEDIFF(N, @ CheckTime ,< span class =code-sdkkeyword> @ TimeNow )/ 60. 00 作为 WorkHours ,
@ DateConvert As OnlyDate, @ TimePlus 作为 OnlyDatePlus
结束
< span class =code-keyword> FETCH NEXT FROM EmpShift INTO @ EmpID , @ ShiftName , @ StartDate , @ EndDate , @ WorkDate , @ CheckTime , @ CheckType , @ DateConvert
结束
关闭 EmpShift
取消分配 EmpShift
结束
解决方案
好的 - 我从你的剧本中看到了这个问题。
你正在打电话给severa l选择语句,每个语句返回一行。
$ C $ b在C#中这可能看起来像一个包含许多表的数据集。每个表都有1行。将此数据集添加为数据源,gridview将仅显示来自1个表的数据。
要解决此问题,您需要构建结果并将其作为单个选择返回。我建议创建一个表变量并填充它。我已经重写了下面的脚本,但请测试并更正它!
ALTER Proc [dbo]。[EmpShiftCal]
As
开始
声明 @ ShiftName varchar ( 50 ),
@CheckTime 时间,
@ WorkDate date ,
@ StartDate date ,
@ EndDate date ,
@EmpID int ,
@ CheckType char ( 8 ),
@ StartTime 时间,
@ AttDate datetime ,
@ TimeNow 时间,
@ TimePlus 日期时间,
@ DateConvert < span class =code-keyword> datetime ,
@ date1 date , @ date2 date
声明 @ ResultTable table
(
EmpId int ,
名称 varchar ( 50 ),
StartDate date ,
EndDate date ,
WorkDate date ,
CheckTime time ,
CheckType char ( 8 ),
TimeNow 时间,
DateAttendance datetime ,
LateInMinutes int ,
WorkHours decimal ( 16 , 2 ),
OnlyDate datetime ,
OnlyDatePlus datetime
)
设置 @ date1 = dateadd(月, - 1,GETDATE());
设置 @ date2 = GetDate()
DECLARE EmpShift CURSOR FOR
选择 EmpShift.EmpID,
EmpShift.ShiftName,
EmpShift.StartDate,
EmpShift.EndDate,
转换( varchar ( 10 ),CHECKINOUT.CheckTime, 121 )作为 WorkDate,
convert ( char ( 5 ),CHECKINOUT.CheckTime, 108 ) As CheckTime,
CHECKINOUT.CheckType,
CHECKINOUT.CheckTime
From EmpShift
内部 加入 CHECKINOUT 开启 EmpShift.EmpID = CHECKINOUT.USERID
其中((EmpShift.StartDate @ date1 AND @ date2 )
AND (EmpShift.EndDate @ date1 AND @ date2 ))
和 EmpShift.EmpID = 191
AND (CHECKINOUT.CheckTime @ Date1 AND @ Date2 )
GROUP BY EmpShift.EmpID,转换( varchar ( 10 ),
CHECKINOUT.CheckTime, 121 ), convert ( char ( 5 ),CHECKINOUT.CheckTime, 108 ),
EmpShift.EmpID,
EmpShift.ShiftName,
EmpShift.StartDate,
EmpShift.EndDate,
CHECKINOUT.CheckType,
CHECKINOUT.CheckTime
order by StartDate
打开 EmpShift
FETCH NEXT FROM EmpShift INTO @EmpID , @ ShiftName , @开始日期, @ EndDate , @ WorkDate , @ CheckTime , @ CheckType , @ DateConvert
(@@ FETCH_STATUS = 0)
BEGIN
选择 @ StartTime = StartTime From ShiftTable 其中 ShiftName = @ ShiftName
if (@ ShiftName!= ' ' 和 @ CheckType = ' 我')
开始
设置 @ TimePlus = DATEADD(DAY, 1 , @ WorkDate )
设置 @ AttDate = datediff(dd, @ WorkDate , @ WorkDate )
设置 @TimeNow =(选择 MAX(CHECKINOUT.CheckTime)来自 CHECKINOUT 其中 CheckTime @AttDate AND @ TimePlus AND CHECKTYPE = ' O' AND USERID = 191)
插入 进入 @ ResultTable (
EmpId,
名称,
StartDate,
EndDate,
WorkDate,
CheckTime,
CheckType,
TimeNow,
DateAttendance,
LateInMinutes,
WorkHours,
OnlyDate,
OnlyDatePlus)
VALUES ( @EmpID ,
@ ShiftName ,
@ StartDate ,
@ EndDate ,
@ WorkDate ,
@ CheckTime ,
@ CheckType ,
< span class =code-sdkkeyword> @ TimeNow ,
@ AttDate ,
DATEDIFF(N, @ CheckTime , @ StartTime ),
DATEDIFF(N, @ CheckTime , @ TimeNow )/ 60. 00 ,
@ DateConvert ,
@ TimePlus )
结束
FETCH NEXT FROM EmpShift INTO @EmpID , @ ShiftName , @ StartDate , @ EndDate , @ WorkDate , @ CheckTime , @ CheckType , @ DateConvert
结束
关闭 EmpShift
取消分配 EmpShift
选择 * 来自 @ ResultTable
结束
Created one stored procedure with cursor , and trying to call the stored procedure from asp.net want to show in gridview the issue is its returns only single row thats the first row only. Is it like that? i need to edit something on the stored procedure. or in the asp code?
ALTER Proc [dbo].[EmpShiftCal]
As
Begin
Declare @ShiftName varchar(50),
@CheckTime time,
@WorkDate date,
@StartDate date,
@EndDate date,
@EmpID int,
@CheckType char(8),
@StartTime Time,
@AttDate datetime,
@TimeNow time,
@TimePlus Datetime,
@DateConvert datetime,
@date1 date,@date2 date
Set @date1 = dateadd(month, -1, GETDATE());
Set @date2 = GetDate()
DECLARE EmpShift CURSOR FOR
Select EmpShift.EmpID,EmpShift.ShiftName,EmpShift.StartDate,EmpShift.EndDate,
Convert(varchar(10), CHECKINOUT.CheckTime, 121) As WorkDate,convert(char(5), CHECKINOUT.CheckTime, 108) As CheckTime,
CHECKINOUT.CheckType,CHECKINOUT.CheckTime
From EmpShift
Inner Join CHECKINOUT On
EmpShift.EmpID=CHECKINOUT.USERID
Where ((EmpShift.StartDate Between @date1 AND @date2) AND (EmpShift.EndDate Between @date1 AND @date2)) And EmpShift.EmpID=191
AND (CHECKINOUT.CheckTime Between @Date1 AND @Date2)
GROUP BY EmpShift.EmpID, Convert(varchar(10), CHECKINOUT.CheckTime, 121),convert(char(5), CHECKINOUT.CheckTime, 108),EmpShift.EmpID,EmpShift.ShiftName,
EmpShift.StartDate,EmpShift.EndDate,CHECKINOUT.CheckType,CHECKINOUT.CheckTime
order by StartDate
Open EmpShift
FETCH NEXT FROM EmpShift INTO @EmpID,@ShiftName,@StartDate,@EndDate,@WorkDate,@CheckTime,@CheckType,@DateConvert
While(@@FETCH_STATUS=0)
BEGIN
Select @StartTime=StartTime From ShiftTable Where ShiftName=@ShiftName
if(@ShiftName!='' and @CheckType='I')
Begin
Set @TimePlus=DATEADD(DAY, 1, @WorkDate)
Set @AttDate=datediff(dd,@WorkDate,@WorkDate)
Set @TimeNow=(Select MAX(CHECKINOUT.CheckTime) From CHECKINOUT Where CheckTime Between @AttDate AND @TimePlus AND CHECKTYPE='O' AND USERID=191)
Select @EmpID As EmpId,@ShiftName As Name,@StartDate As StartDate,@EndDate As EndDate,
@WorkDate As WorkDate,@CheckTime As CheckTime,@CheckType As CheckType,@TimeNow As TimeNow,@AttDate As DateAttendance,
DATEDIFF(N,@CheckTime,@StartTime) As LateInMinutes,
DATEDIFF(N,@CheckTime,@TimeNow)/60.00 As WorkHours,
@DateConvert As OnlyDate,@TimePlus As OnlyDatePlus
End
FETCH NEXT FROM EmpShift INTO @EmpID,@ShiftName,@StartDate,@EndDate,@WorkDate,@CheckTime,@CheckType,@DateConvert
End
Close EmpShift
Deallocate EmpShift
End
解决方案
Ok - I see the issue from your script.
You are calling several "Select" statements, each returning a single row.
in C# this will probably look like a dataset with many tables. each table will have 1 row. Add this dataset as a datasource and the gridview will only show data from 1 table.
To fix this, you need to build the result and return it as a single select. I suggest creating a table variable and populating that. I have re-written the script below but please test and correct it!
ALTER Proc [dbo].[EmpShiftCal] As Begin Declare @ShiftName varchar(50), @CheckTime time, @WorkDate date, @StartDate date, @EndDate date, @EmpID int, @CheckType char(8), @StartTime Time, @AttDate datetime, @TimeNow time, @TimePlus Datetime, @DateConvert datetime, @date1 date,@date2 date Declare @ResultTable table ( EmpId int, Name varchar(50), StartDate date, EndDate date, WorkDate date, CheckTime time, CheckType char(8), TimeNow time, DateAttendance datetime, LateInMinutes int, WorkHours decimal(16,2), OnlyDate datetime, OnlyDatePlus datetime ) Set @date1 = dateadd(month, -1, GETDATE()); Set @date2 = GetDate() DECLARE EmpShift CURSOR FOR Select EmpShift.EmpID, EmpShift.ShiftName, EmpShift.StartDate, EmpShift.EndDate, Convert(varchar(10), CHECKINOUT.CheckTime, 121) As WorkDate, convert(char(5), CHECKINOUT.CheckTime, 108) As CheckTime, CHECKINOUT.CheckType, CHECKINOUT.CheckTime From EmpShift Inner Join CHECKINOUT On EmpShift.EmpID=CHECKINOUT.USERID Where ((EmpShift.StartDate Between @date1 AND @date2) AND (EmpShift.EndDate Between @date1 AND @date2)) And EmpShift.EmpID=191 AND (CHECKINOUT.CheckTime Between @Date1 AND @Date2) GROUP BY EmpShift.EmpID, Convert(varchar(10), CHECKINOUT.CheckTime, 121),convert(char(5), CHECKINOUT.CheckTime, 108), EmpShift.EmpID, EmpShift.ShiftName, EmpShift.StartDate, EmpShift.EndDate, CHECKINOUT.CheckType, CHECKINOUT.CheckTime order by StartDate Open EmpShift FETCH NEXT FROM EmpShift INTO @EmpID,@ShiftName,@StartDate,@EndDate,@WorkDate,@CheckTime,@CheckType,@DateConvert While(@@FETCH_STATUS=0) BEGIN Select @StartTime=StartTime From ShiftTable Where ShiftName=@ShiftName if(@ShiftName!='' and @CheckType='I') Begin Set @TimePlus=DATEADD(DAY, 1, @WorkDate) Set @AttDate=datediff(dd,@WorkDate,@WorkDate) Set @TimeNow=(Select MAX(CHECKINOUT.CheckTime) From CHECKINOUT Where CheckTime Between @AttDate AND @TimePlus AND CHECKTYPE='O' AND USERID=191) insert into @ResultTable( EmpId , Name , StartDate, EndDate , WorkDate, CheckTime , CheckType , TimeNow , DateAttendance , LateInMinutes , WorkHours , OnlyDate , OnlyDatePlus ) VALUES (@EmpID, @ShiftName, @StartDate, @EndDate, @WorkDate, @CheckTime, @CheckType, @TimeNow, @AttDate, DATEDIFF(N,@CheckTime,@StartTime), DATEDIFF(N,@CheckTime,@TimeNow)/60.00, @DateConvert, @TimePlus ) End FETCH NEXT FROM EmpShift INTO @EmpID,@ShiftName,@StartDate,@EndDate,@WorkDate,@CheckTime,@CheckType,@DateConvert End Close EmpShift Deallocate EmpShift select * from @ResultTable End
这篇关于Sql Cursron结果只返回单行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文