Please be aware that the code you have provided is open to a SQL Injection attack. Whenever building dynamic SQL you either need to sanitize the input (very hard), or use sp_executesql to use parameters. Your stored procedure should be written more like this:

ALTER PROC [dbo].[CollectsearchData]

@StartDate DATETIME,

@EndDate DATETIME,

@EmployeeID NVARCHAR(50),

@EmployeeName NVARCHAR(50)

AS

BEGIN

DECLARE

@sqlquery AS NVARCHAR(2000),

@parameters NVARCHAR(2000) = '@StartDate DATETIME, @EndDate DATETIME, @EmployeeID NVARCHAR(50), @EmployeeName NVARCHAR(50)';

SET @sqlquery = 'SELECT * from ViewEmployeeTest Where (1=1)'

IF (@StartDate IS NOT NULL)

SET @sqlquery = @sqlquery + ' And (joindate >= @StartDate)'

IF (@EndDate IS NOT NULL)

SET @sqlquery = @sqlquery + ' And (joindate <= @EndDate)'

IF @EmployeeID <> ''

SET @sqlquery = @sqlquery + 'And (EmployeeID = @EmployeeID)'

IF @EmployeeName IS NOT NULL

SET @sqlquery = @sqlquery +

' AND (DriverName LIKE ''% + '' + @EmployeeName + ''%'') '

PRINT @sqlquery

EXEC sys.sp_executesql @sqlquery, @parameters, @StartDate = @StartDate,

@EndDate = @EndDate, @EmployeeID = @EmployeeID,

@EmployeeName = @EmployeeName;

END

It sounds like the reason you aren't getting data when a date is not passed in is because you aren't getting a NULL passed into the procedure for the date parameters. Evaluate what your application is passing for the parameters and then either convert that value to a NULL in the procedure or check for that value in the procedure. Maybe something like this:

IF (NULLIF(@StartDate, '1900-01-01') IS NOT NULL)