Not getting proper output from SQL Server query

ALTER PROCEDURE [dbo].[Sp_GetEmailForMailing] 
    -- Add the parameters for the stored procedure here
    @end int=100开发者_如何学编程0
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT top(500) RegistrationID, EmailID from dbo.Candidate_RegistrationData
    where status_flag=1 and payment_status=2
    and RegistrationID NOT IN (SELECT top(@end) RegistrationID from dbo.Candidate_RegistrationData
    where status_flag=1 and payment_status=2) order by RegistrationID
END

This is my stored procedure. In this I want rows from 500 but I am getting from 250 row number.... any one have idea why it happen...thank you

The subquery used as the source of data for the NOT IN does not have an ORDER BY clause. As a result, the query engine is free to take the TOP(@end) rows in whichever order it sees fit.

I hope you want to skip top @end registration, Order By RegistrationID

You should try below code.

ALTER PROCEDURE [dbo].[Sp_GetEmailForMailing]   

@end int = 1000  

AS  

SET NOCOUNT ON;

BEGIN
    Select Top(500) RegistrationID, EmailID From
    (
         Select Row_Number() Over(Order by RegistrationID) as RowId, 
                   RegistrationID,     
                   EmailID,    
                   status_flag, 
                   payment_status
         From dbo.Candidate_RegistrationData
         Where status_flag = 1 and payment_status = 2
    )T
    Where RowId > @end and status_flag = 1 and payment_status = 2 
    Order By RegistrationID
End