Getting wrong result from stored procedure

Question

I have an Asp.net MVC 4.0 project and I have tried to get data from a stored procedure in SQL Server 2008. I used IDBConnection with this code:

public override IEnumerable<T> GetItemsBySP(string sp, object @where = null)
{
    var result = @where == null
            ? Connection.Query<T>(getShema() + sp, commandType: CommandType.StoredProcedure)
            : Connection.Query<T>(getShema() + sp, commandType: CommandType.StoredProcedure, param: @where);

    return result;
}

and my stored procedure is:

CREATE PROCEDURE [Cms].[sp_SearchClientInfoByPage]
    @PageSize int = NULL,
    @PageIndex int = NULL,
    @MethodId int = NULL,
    @LanguageId int,
    @CountryId int = NULL,
    @Industry nvarchar(50) = NUlL,
    @WhereDateClause nvarchar(100) = NULL,
    @Company nvarchar(50) = NULL
 AS
 BEGIN
     SET NOCOUNT ON;

     BEGIN TRY
         -- Set default values if (non-db) NULLs passed in
         IF @PageSize is NULL
         BEGIN
             SET @PageSize = 10000
         END

         IF @PageIndex IS NULL
         BEGIN
             SET @PageIndex = 0
         END

         DECLARE @PageLowerBound INT,
                 @PageUpperBound INT,
                 @MainSQL NVARCHAR(1000)

         SET @PageLowerBound = (@PageSize * @PageIndex) + 1
         SET @PageUpperBound=@PageSize  - 1 + @PageLowerBound

         SET @MainSQL ='SELECT ' + CAST(@PageLowerBound as nvarchar(10))+', '
         SET @MainSQL = @MainSQL + CAST(@PageUpperBound as nvarchar(10)) + ' '
         SET @MainSQL = @MainSQL +';WITH CTE AS' +' '
         SET @MainSQL = @MainSQL +'( SELECT ROW_NUMBER() OVER(ORDER BY ClientName ASC) AS RowNumber ,' +' '
         SET @MainSQL = @MainSQL +'COUNT(1) OVER(PARTITION BY NULL) AS TotalRowCount ,ci.*,' +' '
         SET @MainSQL = @MainSQL +'con.CountryName' +' '
         SET @MainSQL = @MainSQL +'FROM [Cms].ClientInfo ci LEFT JOIN Cms.ClientMethod cim ON ci.ClientId=cim.ClientId' +' '
         SET @MainSQL = @MainSQL +'LEFT JOIN core.Country con ON con.CountryId=ci.CountryId'+' '
         SET @MainSQL = @MainSQL +'WHERE ci.LanguageId='+CAST(@LanguageId as nvarchar(10))+ ' '

         -- Method ID
         IF @MethodId IS NOT NULL
         BEGIN
             SET @MainSQL = @MainSQL +'AND cim.MethodId='+ CAST(@MethodId as nvarchar(10))+' '
         END

         IF @CountryId IS NOT NULL
         BEGIN
             SET @MainSQL = @MainSQL +'AND ci.CountryId='+CAST(@CountryId as nvarchar(10))+' '
         END

         IF @Company IS NOT NULL
         BEGIN
             SET @MainSQL = @MainSQL +'AND ('+ @Company+ ' IS NULL )' +' '
         END

         IF @Industry IS NOT NULL
         BEGIN
             SET @MainSQL = @MainSQL +'AND ci.ClientIndustry like ''%'+@Industry+ '%'''
         END

         IF @WhereDateClause IS NOT NULL
         BEGIN
             SET @MainSQL = @MainSQL + @WhereDateClause +' '
         END 

         SET @MainSQL = @MainSQL + ')'
         SET @MainSQL = @MainSQL + ' SELECT CTE.* 
                                     FROM CTE 
                                     WHERE RowNumber BETWEEN '+ CAST(@PageLowerBound as nvarchar(10)) + ' AND ' + CAST(@PageUpperBound as nvarchar(10))+';'

         PRINT(@MainSQL)

         EXEC(@MainSQL)
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage nvarchar(max),
                @ErrorNumber int,
                @ErrorSeverity int,
                @ErrorState int,
                @ErrorLine int,
                @ErrorProcedure  nvarchar(200);

        -- Assign variables to error-handling functions that capture information for RAISERROR.
        SELECT  
            @ErrorNumber = ERROR_NUMBER(), 
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE(), 
            @ErrorLine = ERROR_LINE(),
            @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

        -- Build the message string that will contain original error information.
        SELECT 
            @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: '+ ERROR_MESSAGE();

        -- Only set the error state if its been set to zero
        IF (@ErrorState = 0) SET @ErrorState = 1
            -- Raise an error: msg_str parameter of RAISERROR will contain the original error information.
            RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState,
               @ErrorNumber,    -- parameter: original error number.
               @ErrorSeverity,  -- parameter: original error severity.
               @ErrorState,     -- parameter: original error state.
               @ErrorProcedure, -- parameter: original error procedure name.
               @ErrorLine       -- parameter: original error line number.
            );
    END CATCH

    SET NOCOUNT OFF
END

I ran the stored procedure successfully and result is more than one row. However, I tried to run through Visual Studio then result is always one row and data is not correct.

Anyone can help me?

Answer 1

Please remove below code from your stored procedure:

 SET @MainSQL ='SELECT ' + CAST(@PageLowerBound as nvarchar(10))+', '
 SET @MainSQL = @MainSQL + CAST(@PageUpperBound as nvarchar(10)) + ' '

Because of you already set two variables before:

 SET @PageLowerBound = (@PageSize * @PageIndex) + 1
 SET @PageUpperBound=@PageSize  - 1 + @PageLowerBound

I ran your stored procedure from my end and I saw that the result returns two tables.

Hopefully, it would be helpful for you!