How to find duplicate values in a table

SELECT name,email

FROM users

GROUP BY email

HAVING ( COUNT(email) > 1 )

Dynamic where condition,sorting and paging using stored procedure in sql server

CREATE PROCEDURE [HR.Reports].[EmployeeList]

@Search varchar(50)='',

@SearchBy varchar(50)='',

@PageIndex int = 1,

@PageSize int = 10,

@branch_ID int,

@dept_ID int,

@SortBY varchar(100),

@SortDir varchar(100),

@Date DATE,

@SearchType varchar(100), --category/grade/

@SearchTypeId int --categoryID/gradeID/

AS

BEGIN

SET NOCOUNT ON;

DECLARE @StartRow int

DECLARE @EndRow int

SET @StartRow = (@PageSize * (@PageIndex - 1)) + 1

SET @EndRow = @PageSize * @PageIndex + 1

if(@Search='')set @Search=NULL;

WITH Search AS

(

SELECT

ROW_NUMBER() OVER

(

-- Dynamic sorting

ORDER BY

CASE WHEN @SortBY = 'emp_ID' and @SortDir='asc' THEN empView.emp_ID end Asc ,

CASE WHEN @SortBY = 'emp_ID' and @SortDir='desc' THEN empView.emp_ID end Desc ,

CASE WHEN @SortBY = 'emp_FirstName' and @SortDir='asc' THEN empView.emp_FirstName end Asc ,

CASE WHEN @SortBY = 'emp_FirstName' and @SortDir='desc' THEN empView.emp_FirstName end Desc,

CASE WHEN @SortBY = 'designation' and @SortDir='asc' THEN empView.designation end Asc ,

CASE WHEN @SortBY = 'designation' and @SortDir='desc' THEN empView.designation end Desc ,

CASE WHEN @SortBY = 'grade' and @SortDir='asc' THEN grade end Asc ,

CASE WHEN @SortBY = 'grade' and @SortDir='desc' THEN grade end Desc ,

CASE WHEN @SortBY = 'dept_Name' and @SortDir='asc' THEN dept_Name end Asc ,

CASE WHEN @SortBY = 'dept_Name' and @SortDir='desc' THEN dept_Name end Desc ,

CASE WHEN @SortBY = 'branch_Name' and @SortDir='asc' THEN branch_Name end Asc ,

CASE WHEN @SortBY = 'branch_Name' and @SortDir='desc' THEN branch_Name end Desc ,

CASE WHEN @SortBY = 'category' and @SortDir='asc' THEN category end Asc ,

CASE WHEN @SortBY = 'category' and @SortDir='desc' THEN category end Desc ,

CASE WHEN @SortBY = 'emp_Join_Date' and @SortDir='asc' THEN emp_Join_Date end Asc ,

CASE WHEN @SortBY = 'emp_Join_Date' and @SortDir='desc' THEN emp_Join_Date end Desc ,

CASE WHEN @SortBY = 'Age' and @SortDir='asc' THEN emp_DOB end Asc ,

CASE WHEN @SortBY = 'Age' and @SortDir='desc' THEN emp_DOB end Desc

)

AS RowNumber,

empView.empRef_ID,

empView.emp_ID,

empView.emp_FirstName,

empView.emp_LastName,

empView.[branch_ID],

empView.[emp_Title],

empView.[dept_ID],

empView.designation ,

empView.branch_Name,

empView.dept_Name,

empView.grade,

empView.category,

tblEmpShift.shift_From,

tblEmpShift.shift_To,

empView.emp_DOB,

empView.emp_Join_Date

FROM EmployeeView empView INNER JOIN tblEmpShift ON empView.shift_ID=tblEmpShift.shift_ID

WHERE

(--category/grade/

empView.grade_ID = CASE WHEN @SearchType='grade_ID' THEN ISNULL(@SearchTypeId,grade_ID) END

OR empView.category_ID= CASE WHEN @SearchType='category_ID' THEN ISNULL(@SearchTypeId,category_ID) END

)

AND

empView.dept_ID = CASE WHEN @dept_ID=0 THEN empView.dept_ID ELSE @dept_ID END AND

empView.dept_ID = CASE WHEN @dept_ID=0 THEN empView.dept_ID ELSE @dept_ID END

AND empView.branch_ID= CASE WHEN @branch_ID=0 THEN empView.branch_ID ELSE @branch_ID END

AND (empView.emp_ID Like CASE WHEN @SearchBy='emp_ID' THEN '%' + ISNULL(@Search,emp_ID) + '%' ELSE '¾' END

OR empView.emp_FirstName Like CASE WHEN @SearchBy='emp_FirstName' THEN '%' + ISNULL(@Search,emp_FirstName) + '%' ELSE '¾' END

OR empView.emp_LastName Like CASE WHEN @SearchBy='emp_LastName' THEN '%' +ISNULL( @Search,emp_LastName) + '%' ELSE '¾' END

OR empView.emp_Title Like CASE WHEN @SearchBy='emp_Title' THEN '%' + ISNULL(@Search,emp_Title) + '%' ELSE '¾' END

OR empView.designation Like CASE WHEN @SearchBy='designation' THEN '%' + ISNULL(@Search,designation) + '%' ELSE '¾' END

))

SELECT

a.emp_ID,

a.emp_Title+'. '+ a.emp_FirstName +' '+ a.emp_LastName as empName,

DATEDIFF(yy,a.emp_DOB,@Date)AS Age,

SUBSTRING(CONVERT(VARCHAR,a.shift_From,100),12,8) +' To '+SUBSTRING(CONVERT(VARCHAR,a.shift_To,100),12,8) as WorkShift,

a.designation,

a.branch_Name,

a.dept_Name,

a.grade,

a.category,

CONVERT(VARCHAR, a.emp_Join_Date,105) AS emp_Join_Date,

(SELECT COUNT(*)FROM Search) AS total ,

a.empRef_ID,

a.RowNumber

FROM

Search a

WHERE

(a.RowNumber BETWEEN @StartRow AND @EndRow - 1)

ORDER BY

a.RowNumber

END