Hasmore retruns incorrect value

v8553 · October 26, 2023, 12:26pm

Hi , below is the table , insert script and an logic to return hasmore value
we have diff phases in table
we have pageindex , pagesize and phase as an input to the query
if records exists for next iteration hasmore should return 1 else 0
Problem : current scenario despite there are records for phase = 2 for next iteration hasmore value returning 0 instead of 1
CREATE TABLE [dbo].[temp_CB](
[id] [int] IDENTITY(1,1) NOT NULL,
[ticketID] [int] NULL,
[flag] [int] NULL,
[phase] [int] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[temp_CB] ON
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4000, 14294147, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4001, 14294054, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4002, 14294019, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4003, 14294003, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4004, 14293923, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4005, 14293869, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4006, 14293666, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4007, 14293613, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4008, 14293440, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4009, 14293424, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4010, 14293408, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4011, 14293239, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4012, 14293217, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4013, 14293208, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4014, 14293162, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4015, 14292973, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4016, 14292935, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4017, 14292934, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4018, 14292829, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4019, 14292783, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4020, 14292555, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4021, 14292528, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4022, 14292198, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4023, 14292191, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4024, 14292139, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4025, 14292113, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4026, 14291886, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4027, 14291829, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4028, 14291824, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4029, 14291823, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4030, 14291710, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4031, 14291493, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4032, 14291366, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4033, 14291350, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4034, 14291259, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4035, 14291231, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4036, 14291149, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4037, 14291110, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4038, 14291081, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4039, 14290950, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4040, 14290936, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4041, 14290924, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4042, 14290885, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4043, 14290857, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4044, 14290663, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4045, 14290602, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4046, 14290596, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4047, 14290562, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4048, 14290497, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4049, 14290297, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4050, 14290251, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4051, 14290165, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4052, 14290010, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4053, 14289937, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4054, 14289850, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4055, 14289799, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4056, 14289733, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4057, 14289640, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4058, 14289160, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4059, 14289158, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4060, 14289128, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4061, 14289117, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4062, 14289040, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4063, 14288937, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4064, 14288800, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4065, 14288789, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4066, 14288773, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4067, 14288743, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4068, 14288641, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4069, 14288621, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4070, 14288598, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4071, 14288578, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4072, 14288490, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4073, 14288480, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4074, 14288419, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4075, 14288416, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4076, 14288343, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4077, 14288328, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4078, 14288323, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4079, 14288306, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4080, 14288304, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4081, 14288283, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4082, 14288123, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4083, 14288112, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4084, 14288076, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4085, 14287995, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4086, 14287973, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4087, 14287787, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4088, 14287741, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4089, 14287687, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4090, 14287600, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4091, 14287368, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4092, 14287364, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4093, 14287230, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4094, 14287189, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4095, 14287182, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4096, 14287063, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4097, 14287013, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4098, 14287011, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4099, 14286993, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4100, 14286953, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4101, 14286851, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4102, 14286776, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4103, 14286659, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4104, 14286658, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4105, 14286655, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4106, 14286626, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4107, 14286591, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4108, 14286543, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4109, 14286522, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4110, 14286481, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4111, 14286463, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4112, 14286451, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4113, 14286426, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4114, 14286368, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4115, 14286310, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4116, 14286088, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4117, 14286061, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4118, 14286016, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4119, 14285871, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4120, 14285870, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4121, 14285816, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4122, 14285779, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4123, 14285734, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4124, 14285718, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4125, 14285703, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4126, 14285662, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4127, 14285526, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4128, 14285442, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4129, 14285411, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4130, 14285279, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4131, 14285225, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4132, 14285203, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4133, 14285127, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4134, 14285120, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4135, 14285089, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4136, 14285072, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4137, 14285055, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4138, 14285023, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4139, 14285011, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4140, 14284985, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4141, 14284983, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4142, 14284980, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4143, 14284953, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4144, 14284948, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4145, 14284944, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4146, 14284943, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4147, 14284917, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4148, 14284876, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4149, 14284840, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4150, 14284812, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4151, 14284767, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4152, 14284765, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4153, 14284760, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4154, 14284700, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4155, 14284460, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4156, 14284459, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4157, 14284447, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4158, 14284412, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4159, 14284411, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4160, 14284395, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4161, 14284350, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4162, 14284289, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4163, 14284276, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4164, 14284258, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4165, 14284255, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4166, 14284232, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4167, 14284221, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4168, 14284161, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4169, 14284114, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4170, 14284054, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4171, 14284030, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4172, 14283958, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4173, 14283955, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4174, 14283949, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4175, 14283947, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4176, 14283899, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4177, 14283874, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4178, 14283863, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4179, 14283853, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4180, 14283770, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4181, 14283762, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4182, 14283756, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4183, 14283744, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4184, 14283720, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4185, 14283704, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4186, 14283699, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4187, 14283656, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4188, 14283612, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4189, 14283598, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4190, 14283537, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4191, 14283398, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4192, 14283334, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4193, 14283289, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4194, 14283288, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4195, 14283204, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4196, 14283154, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4197, 14283143, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4198, 14283135, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4199, 14283132, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4200, 14283121, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4201, 14283057, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4202, 14283026, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4203, 14282943, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4204, 14282940, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4205, 14282917, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4206, 14282889, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4207, 14282853, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4208, 14282829, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4209, 14282809, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4210, 14282764, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4211, 14282673, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4212, 14282643, 3, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4213, 14282622, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4214, 14282603, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4215, 14282585, 3, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4216, 14282564, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4217, 14282555, 3, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4218, 14282537, 3, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4219, 14282508, 3, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4220, 14282481, 3, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4221, 14282465, 3, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4222, 14282388, 3, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4223, 14282350, 3, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4224, 14282329, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4225, 14282327, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4226, 14282292, 3, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4227, 14282283, 3, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4228, 14282265, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4229, 14282257, 3, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4230, 14282241, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4231, 14282234, 3, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4232, 14282180, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4233, 14282165, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4234, 14282095, 3, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4235, 14281987, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4236, 14281892, 3, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4237, 14281848, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4238, 14281838, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4239, 14281627, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4240, 14281573, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4241, 14281468, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4242, 14281413, 3, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4243, 14281385, 3, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4244, 14281314, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4245, 14279485, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4246, 14278584, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4247, 14278501, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4248, 14278359, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4249, 14278222, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4250, 14278070, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4251, 14277336, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4252, 14277280, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4253, 14276640, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4254, 14276585, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4255, 14276116, 3, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4256, 14275888, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4257, 14275819, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4258, 14275615, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4259, 14275537, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4260, 14275142, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4261, 14275015, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4262, 14273652, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4263, 14273376, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4264, 14273347, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4265, 14273263, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4266, 14272342, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4267, 14272308, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4268, 14271481, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4269, 14270185, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4270, 14267289, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4271, 14267008, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4272, 14266752, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4273, 14266725, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4274, 14266694, 3, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4275, 14265953, 3, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4276, 14263383, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4277, 14261604, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4278, 14261123, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4279, 14260320, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4280, 14260239, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4281, 14259104, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4282, 14257603, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4283, 14257211, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4284, 14256848, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4285, 14255715, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4286, 14255226, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4287, 14254873, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4288, 14253386, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4289, 14253254, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4290, 14252676, 3, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4291, 14252148, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4292, 14243783, 3, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4293, 14242146, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4294, 14234158, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4295, 14159962, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4296, 14042536, 1, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4297, 13979681, 3, 2)
INSERT [dbo].[temp_CB] ([id], [ticketID], [flag], [phase]) VALUES (4298, 13944485, 1, 2)
SET IDENTITY_INSERT [dbo].[temp_CB] OFF
GO

----insert script end here-----
declare @pageindex int = 21 , @pagesize int = 200 , @Phase int = 2 , @total_index int , @count int , @hasmore int

SELECT @count = COUNT(*) FROM dbo.temp_CB WHERE Phase = @Phase

SET @total_index = CEILING(@count / @pagesize)

IF CAST(@total_index AS INT) > @pageindex
SET @hasmore = 1
ELSE
SET @hasmore = 0

SELECT @hasmore AS 'hasmore'

SELECT *
FROM [dbo].[temp_CB]
WHERE Phase = @Phase AND
ID >= ((@pageindex - 1) * @pagesize) + 1
AND ID <= @pageindex * @pagesize

stepson · October 26, 2023, 12:47pm

Hi,
I don't fully understand what you are trying to do..
First , @total_index is of type INT,
@pageSize is also INT
So, the result of @count / @pagesize is also INT = 1 , (ceiling of 1 is 1)

SELECT 299/200 as outputINT

outputINT
1

you can change by multiply it with 1.0 , like this:

SELECT 299 *1.0/200 as outputINT, CEILING( 299 *1.0/200)

outputINT	ceilingValue
1.495000	2

Then, the IF @total_index > @pageindex ( Is 1 > 21 ? ) is false , then @hasmore = 0

What value, do you expect to be for the @total_index ?

v8553 · October 26, 2023, 1:14pm

Sorry i don't no exactly what should be the total_index

let me explain in this way what i need .. there are total 299 records
if pageindex = 21 and pagesize = 200 so will pull out 200 records out of 299. hasmore should be 1
if pageindex = 22 and pagesize = 200 so will pull out 99 records out of 99 (it will fetch data from 201 to 99) . hasmore should be 0 . Since we don't have further records to pull out from the table.

ScottPletcher · October 26, 2023, 2:05pm

If the pagesize is 200, then pageindex = 21 would return rows 4001 to 4200.

As for fixing the logic for @hasmore:


declare @pageindex int = 21 , @pagesize int = 200 , @Phase int = 2 , @total_index int , @count int , @hasmore int

SELECT @count = COUNT(*) FROM dbo.temp_CB WHERE Phase = @Phase

IF @count > @pageindex * @pagesize --<<--
SET @hasmore = 1
ELSE
SET @hasmore = 0

SELECT @hasmore AS 'hasmore'

SELECT *
FROM [dbo].[temp_CB]
WHERE Phase = @Phase AND
ID >= ((@pageindex - 1) * @pagesize) + 1
AND ID <= @pageindex * @pagesize