CREATE PROCEDURE [dbo].[SiteErrorSelectAll]
(
@Search VARCHAR(MAX)='',
@PageIndex INT=1,
@PageSize INT=5,
@SortExp NVARCHAR(MAX)='ErrorIDDESC',
@IPAddress VARCHAR(50)='',
@Browser VARCHAR(50)='',
@WebURL VARCHAR(1024)='',
@UserMasterID VARCHAR(100)='36'
)
AS
BEGIN TRANSACTION
BEGIN TRY
SELECT Top(@PageSize)
ErrorID, ErrorCode, ExceptionMessage, ExceptionStackTrace,
[Source], IPAddress, Browser, [Description],
WebURL, UserName, ModifiedON, TotalRows
FROM
( SELECT SE.ErrorID,
SE.ErrorCode,
SE.ExceptionMessage,
SE.ExceptionStackTrace,
SE.[Source],
SE.IPAddress,
SE.Browser,
SE.[Description],
SE.WebURL,
UM.UserName,
CONVERT(NVARCHAR(50),SE.ModifiedON,103) AS ModifiedON,
(
ROW_NUMBER() OVER
(
ORDER BY
CASE WHEN @SortExp='ErrorIDASC' THEN SE.ErrorID END ASC,
CASE WHEN @SortExp='ErrorIDDESC' THEN SE.ErrorID END DESC,
CASE WHEN @SortExp='ErrorCodeASC' THEN SE.ErrorCode END ASC,
CASE WHEN @SortExp='ErrorCodeDESC' THEN SE.ErrorCode END DESC
)
) AS RowNumber,
TotalRows=Count(*) OVER()
FROM SiteError SE
LEFT JOIN UserMaster UM ON UM.UserMasterID=SE.ModifiedBY
WHERE
(
LOWER(SE.ErrorCode) LIKE '%'+LOWER(@Search)+'%' OR
LOWER(SE.ExceptionMessage) LIKE '%'+LOWER(@Search)+'%' OR
LOWER(SE.ExceptionStackTrace) LIKE '%'+LOWER(@Search)+'%' OR
LOWER(SE.[Source]) LIKE '%'+LOWER(@Search)+'%' OR
LOWER(SE.IPAddress) LIKE '%'+LOWER(@Search)+'%' OR
LOWER(SE.Browser) LIKE '%'+LOWER(@Search)+'%' OR
LOWER(SE.[Description]) LIKE '%'+LOWER(@Search)+'%' OR
LOWER(SE.WebURL) LIKE '%'+LOWER(@Search)+'%' OR
LOWER(UM.UserName) LIKE '%'+LOWER(@Search)+'%' OR
LOWER(SE.ModifiedON) LIKE '%'+LOWER(@Search)+'%'
)
) AS ErrorWithRowNumber
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1
AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
BEGIN TRANSACTION siteerror
Declare @ErrorCode VARCHAR(50), @ExceptionMessage varchar(max), @Source varchar(512),@ErrorDesc varchar(max)
set @ErrorCode=(select CAST(ISNULL(ERROR_NUMBER(),'') AS VARCHAR))
set @Source=(select ISNULL(ERROR_PROCEDURE(),''))
set @ExceptionMessage=(select ISNULL(ERROR_MESSAGE(),''))
set @ErrorDesc=(select 'Severity : ' + CAST(ISNULL(ERROR_SEVERITY(),'') AS VARCHAR(50)) + ' State : ' + CAST(ISNULL(ERROR_STATE(),'') AS VARCHAR(50)) + ' Line : ' + CAST(ISNULL(ERROR_LINE(),'') AS VARCHAR(50)))
EXECUTE [dbo].[SiteErrorInsert]
@ErrorCode,@ExceptionMessage,'',@Source,@IPAddress,@Browser,@ErrorDesc,@WebURL,@UserMasterID
COMMIT TRANSACTION siteerror
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
No comments:
Post a Comment