Nga Đỗ Nga Đỗ - 3 months ago 6
C# Question

Getting wrong result from stored procedure

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

Please remove below code from your store 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 store from my end and I saw that the result returns two tables.

Hopefully, it would be helpful for you!