CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
Bulla
Bulla

Reputation: 70

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

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

Upvotes: 1

Views: 1132

Answers (3)

Sanjay
Sanjay

Reputation: 515

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

Upvotes: 1

FDavidov
FDavidov

Reputation: 3667

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)

Upvotes: 0

Squirrel
Squirrel

Reputation: 24793

you need to enclosed your date string in single quote

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

Upvotes: 1

Related Questions