Many times i see that programmers confused with search data in the sql server table.Here i am describing for random data/full data/empty data transfer value from UI ,where data can search easily .I am presenting behalf of this question to answer with Stored procedure.
USE [sqldb14]
GO
/****** Object: StoredProcedure [dbo].[Sp_DM_filter_Employee_fromto] Script Date: 04/24/2014 11:34:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create proc [dbo].[Sp_DM_filter_Employee_fromto]
@customer varchar(100)=NULL,
@employeename varchar(100)=NULL,
@edatefrom varchar(100)= NULL,
@edateto varchar(100)= NULL
AS
--declare @employeename varchar(100)
--declare @customer varchar(100)
--declare @edatefrom varchar(100)
--declare @edateto varchar(100)
--set @employeename =''
--set @customer=''
--set @edatefrom='26 Mar 2014'
--set @edateto=''
Select emprep.Id,emprep.Title,emprep.email,emprep.Description,emprep.Date,emp.name,emp.email,emprep.Doc,emprep.CustomerId from tb_DM_Report_Employee as emprep
inner join tb_DM_Employee as emp on emprep.email=emp.email
WHERE
(emprep.CustomerId LIKE '%' + @customer + '%' OR @customer='' OR @customer IS NULL)
--AND (emp.name LIKE '%' + @employeename + '%' OR @employeename='' OR @employeename IS NULL)
AND (emp.email LIKE '%' + @employeename + '%' OR @employeename='' OR @employeename IS NULL)
AND (cast(emprep.Date as datetime)>=cast(@edatefrom as datetime) OR @edatefrom='' OR @edatefrom IS NULL)
--AND (cast(emprep.Date as datetime)>=cast(@edatefrom as datetime) OR @edatefrom='' OR @edatefrom IS NULL)
AND (cast(emprep.Date as datetime)<=cast(@edateto as datetime) OR @edateto='' OR @edateto IS NULL)
--AND (cast(emprep.Date as datetime) <=@edatefrom OR @edatefrom='' OR @edatefrom IS NULL)
and emprep.Isdeleted='False' ORDER BY emprep.Id desc
--AND ((emprep.Date >=emprep.Date=@edateto OR @edateto='' OR @edateto IS NULL) AND (emprep.Date <=emprep.Date=@edatefrom OR @edatefrom='' OR @edatefrom IS NULL))
--[select * from tb_DM_Employee
If this blog is helping you .Please give thank to C# Corner.