****************************** THE SP
ALTER PROCEDURE [dbo].[caw_XYZs_GridView]
@startrec int
, @endrec int
, @statusfilterid int
, @filter_XYZname varchar(50)
, @filter_skucount varchar(50)
, @sortcolumn varchar(2)
, @sortdir varchar(4)
AS
BEGIN
/*------------------------------------------------------------------------------------------------------------------------------------------------------
Modified Date: 12-14-2015
------------------------------------------------------------------------------------------------------------------------------------------------------*/
BEGIN TRY
DECLARE @TotalRows numeric(16, 2)
IF @filter_XYZname IS NULL
AND @filter_skucount IS NULL
BEGIN
SELECT @TotalRows = count(*)
FROM vw_XYZs_List
SELECT XYZID
, XYZName
, SKUCount
FROM vw_XYZs_List
ORDER BY CASE WHEN @sortcolumn = '1' AND @sortdir = 'ASC' THEN XYZName END,
CASE WHEN @sortcolumn = '1' AND @sortdir = 'DESC' THEN XYZName END desc,
CASE WHEN @sortcolumn = '2' AND @sortdir = 'ASC' THEN SKUCount END,
CASE WHEN @sortcolumn = '2' AND @sortdir = 'DESC' THEN SKUCount END Desc
OFFSET @startRec ROWS
FETCH NEXT @endRec ROWS ONLY
END
ELSE
BEGIN
SELECT @TotalRows = count(*)
FROM vw_XYZs_List
WHERE (@filter_XYZname IS NULL OR XYZName like ('%' + @filter_XYZName + '%'))
AND (@filter_skucount IS NULL OR SKUCount LIKE ('%' + @filter_SKUCount + '%'))
SELECT XYZID
, XYZName
, SKUCount
FROM vw_XYZs_List
WHERE (@filter_XYZname IS NULL OR XYZName like ('%' + @filter_XYZName + '%'))
AND (@filter_skucount IS NULL OR SKUCount LIKE ('%' + @filter_SKUCount + '%'))
ORDER BY CASE WHEN @sortcolumn = '1' AND @sortdir = 'ASC' THEN XYZName END,
CASE WHEN @sortcolumn = '1' AND @sortdir = 'DESC' THEN XYZName END desc,
CASE WHEN @sortcolumn = '2' AND @sortdir = 'ASC' THEN SKUCount END,
CASE WHEN @sortcolumn = '2' AND @sortdir = 'DESC' THEN SKUCount END Desc
OFFSET @startRec ROWS
FETCH NEXT @endRec ROWS ONLY
END
SELECT TotalRows
, TotalPages
FROM dbo.fun_PagingAndRows(@TotalRows)
END TRY
BEGIN CATCH
DECLARE @ErrMsg nvarchar(4000),
@ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
SELECT @@TRANCOUNT
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
END
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
END
ALTER PROCEDURE [dbo].[caw_XYZs_GridView]
@startrec int
, @endrec int
, @statusfilterid int
, @filter_XYZname varchar(50)
, @filter_skucount varchar(50)
, @sortcolumn varchar(2)
, @sortdir varchar(4)
AS
BEGIN
/*------------------------------------------------------------------------------------------------------------------------------------------------------
Modified Date: 12-14-2015
------------------------------------------------------------------------------------------------------------------------------------------------------*/
BEGIN TRY
DECLARE @TotalRows numeric(16, 2)
IF @filter_XYZname IS NULL
AND @filter_skucount IS NULL
BEGIN
SELECT @TotalRows = count(*)
FROM vw_XYZs_List
SELECT XYZID
, XYZName
, SKUCount
FROM vw_XYZs_List
ORDER BY CASE WHEN @sortcolumn = '1' AND @sortdir = 'ASC' THEN XYZName END,
CASE WHEN @sortcolumn = '1' AND @sortdir = 'DESC' THEN XYZName END desc,
CASE WHEN @sortcolumn = '2' AND @sortdir = 'ASC' THEN SKUCount END,
CASE WHEN @sortcolumn = '2' AND @sortdir = 'DESC' THEN SKUCount END Desc
OFFSET @startRec ROWS
FETCH NEXT @endRec ROWS ONLY
END
ELSE
BEGIN
SELECT @TotalRows = count(*)
FROM vw_XYZs_List
WHERE (@filter_XYZname IS NULL OR XYZName like ('%' + @filter_XYZName + '%'))
AND (@filter_skucount IS NULL OR SKUCount LIKE ('%' + @filter_SKUCount + '%'))
SELECT XYZID
, XYZName
, SKUCount
FROM vw_XYZs_List
WHERE (@filter_XYZname IS NULL OR XYZName like ('%' + @filter_XYZName + '%'))
AND (@filter_skucount IS NULL OR SKUCount LIKE ('%' + @filter_SKUCount + '%'))
ORDER BY CASE WHEN @sortcolumn = '1' AND @sortdir = 'ASC' THEN XYZName END,
CASE WHEN @sortcolumn = '1' AND @sortdir = 'DESC' THEN XYZName END desc,
CASE WHEN @sortcolumn = '2' AND @sortdir = 'ASC' THEN SKUCount END,
CASE WHEN @sortcolumn = '2' AND @sortdir = 'DESC' THEN SKUCount END Desc
OFFSET @startRec ROWS
FETCH NEXT @endRec ROWS ONLY
END
SELECT TotalRows
, TotalPages
FROM dbo.fun_PagingAndRows(@TotalRows)
END TRY
BEGIN CATCH
DECLARE @ErrMsg nvarchar(4000),
@ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
SELECT @@TRANCOUNT
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
END
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
END
******************************* THE FUNCTION
ALTER FUNCTION [dbo].[fun_PagingAndRows]
(
@totalrows int
)
RETURNS @rows TABLE
(
TotalRows int
, TotalPages int
)
AS
BEGIN
/*------------------------------------------------------------------------------------------------------------------------------------------------------
Modified Date: 10-01-2015
------------------------------------------------------------------------------------------------------------------------------------------------------*/
INSERT INTO @rows
(
TotalRows
, TotalPages
)
SELECT ISNULL(CONVERT(int, @TotalRows), 0) AS TotalRows
, CASE WHEN (CEILING(ISNULL(@TotalRows, 0) / 10.0)) < 1 THEN 1
ELSE (CEILING(ISNULL(@TotalRows, 0) / 10.0))
END AS TotalPages
RETURN
END