USE [CustomerManagementCenter]
GO
/****** Object: StoredProcedure [dbo].[sp_SearchCustomers] Script Date: 11/01/2010 13:51:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Radeon Ling>
-- Create date: <2010-10-27>
-- Description: <Search the customers for customer management page>
-- =============================================
ALTER PROCEDURE [dbo].[sp_SearchCustomers]
-- Add the parameters for the stored procedure here
(
@SortColumns VARCHAR(100)
, @GroupName VARCHAR(50)
, @StartRow INT
, @PageSize INT
, @CustomerLevel VARCHAR(50)
, @DataSource VARCHAR(50)
, @CustomerName VARCHAR(50)
, @City VARCHAR(50)
, @MobileNumber VARCHAR(50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @CountRows INT;
WITH PagedResults AS
(
SELECT CB.CustomerLevel
,CB.Customerid
,CB.DataSource
,CB.CustomerName
,CB.City
,CB.MobileNumber
,ROW_NUMBER() OVER (ORDER BY @SortColumns) AS ResultSetRowNumber
FROM tb_Customers_Basic AS CB LEFT JOIN tb_Message_CustomerInGroup AS MCIG ON MCIG.Customerid = CB.Customerid
LEFT JOIN dbo.tb_Message_CustomerGroup_Basic AS MCGB ON MCIG.GroupID = MCGB.GroupID
WHERE CB.ActiveStatus=1
AND
(
(@GroupName<>'' and MCGB.GroupName = @GroupName)
OR (@GroupName='' and MCGB.GroupName is null )
OR (@GroupName='' and MCGB.GroupName is not null )
OR (@GroupName IS NULL AND MCGB.GroupName is not null)
)
AND
(
(@CustomerLevel<>'' and CB.CustomerLevel = @CustomerLevel)
OR (@CustomerLevel='' and CB.CustomerLevel is null )
OR (@CustomerLevel='' and CB.CustomerLevel is not null )
OR (@CustomerLevel IS NULL AND CB.CustomerLevel is not null)
)
AND
(
(@DataSource<>'' and CB.DataSource = @DataSource)
OR (@DataSource='' and CB.DataSource is null )
OR (@DataSource='' and CB.DataSource is not null )
OR (@DataSource IS NULL AND CB.DataSource is not null)
)
AND
(
(@CustomerName<>'' and CB.CustomerName = @CustomerName)
OR (@CustomerName='' and CB.CustomerName is null )
OR (@CustomerName='' and CB.CustomerName is not null )
OR (@CustomerName IS NULL AND CB.CustomerName is not null)
)
AND
(
(@City<>'' and CB.City = @City)
OR (@City='' and CB.City is null )
OR (@City='' and CB.City is not null )
OR (@City IS NULL AND CB.City is not null)
)
AND
(
(@MobileNumber<>'' and CB.MobileNumber = @MobileNumber)
OR (@MobileNumber='' and CB.MobileNumber is null )
OR (@MobileNumber='' and CB.MobileNumber is not null )
OR (@MobileNumber IS NULL AND CB.MobileNumber is not null)
)
)
SELECT *
FROM PagedResults
WHERE PagedResults.ResultSetRowNumber > @StartRow
AND PagedResults.ResultSetRowNumber <= (@StartRow + @PageSize);
WITH PagedResults AS
(
SELECT CB.CustomerLevel
,CB.DataSource
,CB.CustomerName
,CB.City
,CB.MobileNumber
,ROW_NUMBER() OVER (ORDER BY @SortColumns) AS ResultSetRowNumber
FROM tb_Customers_Basic AS CB LEFT JOIN tb_Message_CustomerInGroup AS MCIG ON MCIG.Customerid = CB.Customerid
LEFT JOIN dbo.tb_Message_CustomerGroup_Basic AS MCGB ON MCIG.GroupID = MCGB.GroupID
WHERE CB.ActiveStatus=1
AND
(
(@GroupName<>'' and MCGB.GroupName = @GroupName)
OR (@GroupName='' and MCGB.GroupName is null )
OR (@GroupName='' and MCGB.GroupName is not null )
OR (@GroupName IS NULL AND MCGB.GroupName is not null)
)
AND
(
(@CustomerLevel<>'' and CB.CustomerLevel = @CustomerLevel)
OR (@CustomerLevel='' and CB.CustomerLevel is null )
OR (@CustomerLevel='' and CB.CustomerLevel is not null )
OR (@CustomerLevel IS NULL AND CB.CustomerLevel is not null)
)
AND
(
(@DataSource<>'' and CB.DataSource = @DataSource)
OR (@DataSource='' and CB.DataSource is null )
OR (@DataSource='' and CB.DataSource is not null )
OR (@DataSource IS NULL AND CB.DataSource is not null)
)
AND
(
(@CustomerName<>'' and CB.CustomerName = @CustomerName)
OR (@CustomerName='' and CB.CustomerName is null )
OR (@CustomerName='' and CB.CustomerName is not null )
OR (@CustomerName IS NULL AND CB.CustomerName is not null)
)
AND
(
(@City<>'' and CB.City = @City)
OR (@City='' and CB.City is null )
OR (@City='' and CB.City is not null )
OR (@City IS NULL AND CB.City is not null)
)
AND
(
(@MobileNumber<>'' and CB.MobileNumber = @MobileNumber)
OR (@MobileNumber='' and CB.MobileNumber is null )
OR (@MobileNumber='' and CB.MobileNumber is not null )
OR (@MobileNumber IS NULL AND CB.MobileNumber is not null)
)
)
SELECT count(CustomerName) FROM PagedResults;
END