Sql Cursron Result Return only single row

Question

0.00/5 (No votes)

See more:

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?

SQL

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

Posted 9-Jun-15 22:28pm

tastini

Updated 9-Jun-15 22:39pm

v2

Add a Solution

Comments

Andy Lanng 10-Jun-15 4:37am

probably. Couldn't tell you. My mind reading powers only have a 1000 mile range :P

Please post the scripts so we can see what's happening

tastini 10-Jun-15 4:40am

please check now... i update the question

Kornfeld Eliyahu Peter 10-Jun-15 4:45am

If your run the select statements without all the cursor thing - how many records do you get?

tastini 10-Jun-15 4:51am

this procedure returns correct data while am running from sql management studio. same procedure i am trying to call from asp.net gridview that time return only single row.

Andy Lanng 10-Jun-15 5:09am

that's not entirely correct. you won't see one table but many tables, each with one row. Is that correct?

Anyway - comment on my solution if it's not

1 solution

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)

Andy Lanng · Accepted Answer · 2015-06-09T22:59:00

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!

SQL

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