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 ,

The result you expect can be achieved according to the following code：

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

Test code：

 alter procedure b_stu  
 (@poll int)  
 as  
 select EmpID,EmpName,SalaryCalc,(select count(*) from dbo.Employee ) Ycount from dbo.employee   
 where  EmpID between @Poll*10-9 and @Poll*10   
   
 execute b_stu @poll=1  

If the response is helpful, please click "Accept Answer" and upvote it.

Best Regards
Echo

Answer 2

Hi @Rock Hitman ,
Sorry，please refer to below sql:

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

Test code:

 alter procedure b_stu  
 (@poll int)  
 as  
 select EmpID,EmpName,SalaryCalc,case when EmpID=@Poll*10-9 then (select count(*) from dbo.Employee ) end Ycount    
 from dbo.employee   
 where  EmpID between @Poll*10-9 and @Poll*10   
   
 execute b_stu @poll=1  

Regards
Echo

Answer 3

Hi @Rock Hitman ,

Has your problem been solved? if it has been solved,please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding!

Best Regards
Echo

Answer 4

Hi @Rock Hitman ,

Has your problem been solved? if it has been solved,please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding!

Best Regards
Echo

Answer 5

Don't understand your needs.
To return case when Poll = 1, it should Poll 1 to 1000 records
case when Poll = 2, it should Poll 1001 to 2000 records...
case when Poll 3, it should Poll 2001 to 3000 records......and so on:

      ALTER PROCEDURE [dbo].[usp_GeicoData_Select]  
            (@poll int)  
       AS  
            Select  [ID]  
                   ,[VIN]  
                   ,[Make]  
                   ,[Model]  
                   ,[Year]  
        ,case when [ID]=@Poll*10-9 then (select count(*) from [dbo].[usp_GeicoData_Select] ) end Ycount  
                   FROM [TEST].[dbo].[Ins_Data]  
                   Where [ID] between @Poll*1000-999 and @Poll*1000  
                   ORDER BY [ID]  
                             
      execute [dbo].[usp_GeicoData_Select] @poll=1    

In addition, generally speaking, no one will read the case half a month ago. Today I happened to look through a previous post and happened to see your question. Your correct approach should be to accept the answer to this question and post again with a new question.
Also, the experts have worked hard for your question, and accepting the answer is also affirmation of them.

Echo