jeudi 9 juin 2016

Testing a stored procedure to make sure pagging logic is returning all rows

I have a process that uses C# to call a Microsoft Dynamics Web Service to get all projects in the system. A simple select tells me there are 7395 projects but after running my process I only have 6955 projects returned. I am trying to debug this to see if there is something wrong with the paging that is in the stored procedure or if there is something wrong with how I am handing the returned data inside my application. The problem is I am not sure how to go about testing the procedure as it deals with paging and returning subsets of row results. What would be the recommended way of testing out this script?

Here is the stored procedure I need to test:

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'WSL_ProjectSearchByDate')
BEGIN
    DROP PROC WSL_ProjectSearchByDate;
END
GO

CREATE PROC dbo.WSL_ProjectSearchByDate
    @page int,
    @size int,
    @sort nvarchar(200),
    @parm1 nvarchar(50) --Search Date
AS
    SET NOCOUNT ON;

    DECLARE @STMT nvarchar(MAX),
            @lBound int,
            @uBound int

    IF (@sort = '')
    BEGIN
        SET @sort = 'Project';
    END

    IF (@page = 0)
    BEGIN
        SET @STMT =
            'SELECT project AS Project,
                    project_desc AS Description,
                    status_gl AS Status
             FROM dbo.PJPROJ WITH (NOLOCK)
             WHERE crtd_datetime >= ''' + @parm1 + '''
             OR lupd_datetime >= ''' + @parm1 + ''';';
    END
    ELSE
    BEGIN
        IF (@page < 1)
        BEGIN
            SET @page = 1;
        END

        IF (@size < 1)
        BEGIN
            SET @size = 1;
        END

        SET @lBound = (@page -1) * @size;
        SET @uBound = @page * @size + 1;

        SET @STMT =
            'WITH PagingCTE AS
             (
                SELECT  TOP(' + CONVERT(varchar(9), @uBound - 1) + ')
                        project,
                        project_desc,
                        status_gl,
                        ROW_NUMBER() OVER
                        (
                            ORDER BY ' + @sort + '
                        ) AS row
                FROM dbo.PJPROJ WITH (NOLOCK)
                WHERE crtd_datetime >= ''' + @parm1 + '''
                OR lupd_datetime >= ''' + @parm1 + '''
             )
             SELECT project AS Project,
                    project_desc AS Description,
                    status_gl AS Status
             FROM PagingCTE
             WHERE row > ' + CONVERT(varchar(9), @lBound) + '
             AND row < ' + CONVERT(varchar(9), @uBound) + '
             ORDER BY row';
    END

    EXEC sp_executesql @STMT, N'@page int, @size int, @sort nvarchar(200), @param1 nvarchar(12)', @page, @size, @sort, @parm1;

Aucun commentaire:

Enregistrer un commentaire