SQL Server stored procedure to search using various fields along with date range

Question

I've created a stored procedure to get search data using multiple fields including date range. My procedure returns data successfully by all the search parameters but not from date range.

Here is my stored procedure:

ALTER PROCEDURE getAssessmentDetails
    @admin VARCHAR(100),
    @SearchText VARCHAR(500) = '',
    @Status VARCHAR(500) = '',
    @Supervisor VARCHAR(500) = '',
    @Process VARCHAR(500) = '',
    @Skill VARCHAR(500) = '',
    @Designation VARCHAR(500) = '',
    @StartDate DATETIME = '',
    @EndDate DATETIME = ''
AS
BEGIN
    DECLARE @Query VARCHAR(MAX)=''
    DECLARE @Params VARCHAR(MAX)=''

    SELECT @StartDate = CONVERT(varchar, @StartDate, 120), @EndDate = CONVERT(varchar, @EndDate, 120)
    SET @Query = 'select * from ases_admin WITH(NOLOCK) where admin = '  + @admin + ' and timestamp between '+@StartDate+ ' and ' +@EndDate 

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    IF @SearchText<>''
    BEGIN
        SET @Params+=' AND (asesname LIKE ''%'+@SearchText+'%'' ) '
    END

    IF @Status<>''
    BEGIN
        SET @Params+=' AND asesstatus IN('+@Status+') '
    END

    IF @Supervisor<>''
    BEGIN
        SET @Params+=' AND supecode IN('+@Supervisor+') '
    END

    IF @Process<>''
    BEGIN
        SET @Params+=' AND procid IN('+@Process+') '
    END

    IF @Skill<>''
    BEGIN
        SET @Params+=' AND skid IN('+@Skill+') '
    END

    IF @Designation<>''
    BEGIN
        SET @Params+=' AND desigid IN('+@Designation+') '
    END

    EXEC (@Query+@Params)
END
GO

When I input the date as @StartDate = '2018-02-08' and @EndDate = '2018-05-07', I get the following error -

Conversion failed when converting date and/or time from character string

FYI - I am using node.js with SQL Server and Angular.js

Answer 1

you need to enclosed your date string in single quote

+ ' and timestamp between ''' +@StartDate+ ''' and ''' +@EndDate+ '''' 

Answer 2

Instead of writing dynamic query and checking blank for every parameters just write the normal query and execute. For example just write,

select * from ases_admin WITH(NOLOCK) where admin = @admin
and timestamp between @StartDate and  @EndDate
and (asesname LIKE' %'+@SearchText+'%' or @SearchText='')

the last condition in bracket works fine both when searchtext is blank '' or has some text. You can append other conditions like that

Answer 3

You are trying to concatenate a DATETIME variable to a string.

Try this:

SET @Query = 'select * from ases_admin WITH(NOLOCK) where admin = '  + 
             @admin + ' and timestamp between '        + 
             CONVERT(VARCHAR,@StartDate, 121) + ' and ' +
             CONVERT(VARCHAR,@EndDate  , 121)