Nga Đỗ Nga Đỗ - 1 year ago 56
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 Source

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!