Not getting proper output from SQL Server query

Question

ALTER PROCEDURE [dbo].[Sp_GetEmailForMailing] 
    -- Add the parameters for the stored procedure here
    @end int=1000
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

You mean to say, first @end registration will be skipped? order by registrationID ascending ? — Pankaj
– Pankaj, Commented Jun 4, 2011 at 9:27

Will A · Accepted Answer · 2011-06-04 07:57:36Z

3

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.

answered Jun 4, 2011 at 7:57

Will A

25k5 gold badges54 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pankaj · Accepted Answer · 2011-06-04 09:48:49Z

0

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

edited Jun 4, 2011 at 9:48

answered Jun 4, 2011 at 9:23

Pankaj

10.2k40 gold badges152 silver badges298 bronze badges

Collectives™ on Stack Overflow

Not getting proper output from SQL Server query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related