Storedproc - selecting records based on parameter passed

Question

ALTER PROCEDURE [dbo].[usp_GeicoData_Select]  
     (  
           @Year int = NULL   
     )  
AS  
     Select  [ID]  
            ,[VIN]  
            ,[Make]  
            ,[Model]  
            ,[Year]  
            FROM [TEST].[dbo].[Ins_Data]  
            Where [Year] = @Year  
            ORDER BY [ID]  
             
  
GO  

My requirement is, I will be passing another parameter 'Poll' which sends in the input as 1, 2, 3....
If Poll comes in the input as 1, I have to fetch first 10 records.
If Poll comes in the input as 2, I have to fetch records from 11 to 20.
If Poll comes in the input as 3, I have to select records from 21 to 30.

Please advise how can I achieve this through this stored procedure ?

Answer 1

Hi @Rock Hitman ,

Please check:

ALTER PROCEDURE [dbo].[usp_GeicoData_Select]  
      (@Year int = NULL,   
       @Poll int)  
 AS  
 begin  
 if @Poll=1  
  begin  
     Select  [ID],[VIN],[Make],[Model],[Year] FROM [TEST].[dbo].[Ins_Data] Where id<=10 and [Year] = @Year ORDER BY [ID]  
  end  
     else if @Poll=2  
       begin   
      Select  [ID],[VIN],[Make],[Model],[Year] FROM [TEST].[dbo].[Ins_Data] Where 10<id<=20 and [Year] = @Year ORDER BY [ID]              
       end  
         else   
           begin   
             Select  [ID],[VIN],[Make],[Model],[Year] FROM [TEST].[dbo].[Ins_Data] Where 20<id<=30 and [Year] = @Year ORDER BY [ID]     
           end  
end  
  
execute [dbo].[usp_GeicoData_Select] @Poll=1  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Best Regards
Echo

Answer 2

Hi @Rock Hitman ,

Another method is applicable to id=1, 2, 3, 4...
Please check:

ALTER PROCEDURE [dbo].[usp_GeicoData_Select]  
      (@Year int = NULL,   
       @poll int)  
 AS  
      Select  [ID]  
             ,[VIN]  
             ,[Make]  
             ,[Model]  
             ,[Year]  
             FROM [TEST].[dbo].[Ins_Data]  
             Where [Year] = @Year and ([ID] between @poll*10-9 and @poll*10)  
             ORDER BY [ID]  
                 
execute [dbo].[usp_GeicoData_Select] @poll=1    

If you want to return the result you expect,it is best to remove @year after where.Otherwise, @year will filter out some data:

ALTER PROCEDURE [dbo].[usp_GeicoData_Select]  
      (@poll int)  
 AS  
      Select  [ID]  
             ,[VIN]  
             ,[Make]  
             ,[Model]  
             ,[Year]  
             FROM [TEST].[dbo].[Ins_Data]  
             Where [ID] between @Poll*10-9 and @Poll*10  
             ORDER BY [ID]  
                 
execute [dbo].[usp_GeicoData_Select] @poll=1    

Best Regards
Echo

Answer 3

Hi @Rock Hitman ,

This is my test statement, it can be executed successfully：

Create Table dbo.Employee (EmpID int null, EmpName varchar(25) null, SalaryCalc money null, HireDateYear int null )  
Insert into dbo.Employee values  
(1 , 'Tom', '5000', 2015 ),  
(2 , 'Tom', '8000', 2015 ),  
(3 , 'Tom', '9000', 2016 ),  
(4 , 'Sam', '4000', 2016 ),  
(5 , 'Tom', '5000', 2015 ),  
(6 , 'Tom', '8000', 2015 ),  
(7 , 'Tom', '9000', 2016 ),  
(8 , 'Sam', '4000', 2016 ),  
(9 , 'Tom', '5000', 2015 ),  
(10 , 'Tom', '8000', 2015 ),  
(11 , 'Tom', '9000', 2016 ),  
(12 , 'Sam', '4000', 2016 ),  
(13 , 'Tom', '5000', 2015 ),  
(14 , 'Tom', '8000', 2015 ),  
(15 , 'Tom', '9000', 2016 ),  
(16 , 'Sam', '4000', 2016 ),  
(17 , 'Tom', '9000', 2016 ),  
(18 , 'Sam', '4000', 2016 ),  
(19 , 'Tom', '5000', 2015 ),  
(20 , 'Tom', '8000', 2015 ),  
(21 , 'Tom', '9000', 2016 ),  
(22 , 'Sam', '4000', 2016 ),  
(23 , 'Tom', '5000', 2015 ),  
(24 , 'Tom', '8000', 2015 ),  
(25 , 'Tom', '9000', 2016 ),  
(26 , 'Sam', '4000', 2016 ),  
(27 , 'Tom', '9000', 2016 ),  
(28 , 'Sam', '4000', 2016 ),  
(29 , 'Tom', '5000', 2015 ),  
(30 , 'Tom', '8000', 2015 ),  
(31 , 'Tom', '9000', 2016 ),  
(32 , 'Sam', '4000', 2016 ),  
(33 , 'Tom', '5000', 2015 ),  
(34 , 'Tom', '8000', 2015 ),  
(35 , 'Tom', '9000', 2016 ),  
(36 , 'Sam', '4000', 2016 ),  
(37 , 'Tom', '9000', 2016 ),  
(38 , 'Sam', '4000', 2016 ),  
(39 , 'Tom', '5000', 2015 ),  
(40 , 'Tom', '8000', 2015 )  
alter procedure b_stu  
(@poll int)  
as  
select EmpID,EmpName,SalaryCalc from dbo.employee   
where  EmpID between @Poll*10-9 and @Poll*10  
  
execute b_stu @poll=1  
execute b_stu @poll=2  
execute b_stu @poll=3  

Best Regards
Echo

Answer 4

Add the @joon parameter and check if a query like this works for you:

;  
with CTE as (  
   Select *, ROW_NUMBER() over (order by ID) as rn  
    FROM [TEST].[dbo].[Ins_Data]  
    Where [Year] = @Year  
)  
Select  [ID],  
           [VIN],  
           [Make],  
           [Model],  
           [Year]  
From CTE  
where rn between (@Poll - 1) * 10 + 1 and @Poll * 10   
order by ID  

Answer 5

For this you can use the OFFSET clause in the select statement =>

declare @poll int = 3;

select *
from sys.objects
order by object_id
offset @poll * 10 rows fetch next 10 rows only