creating dynamic query in variable

Question

I am creating a dynamic query in sql server 2005 stored procedure and I am getting error if dates are sent empty/null to the stored procedure that:

This input parameter cannot be converted

So, how to check in Stored procedure that covert only id date is not empty: below is my query. I have checked the null in if condition but then also it is showing me error

If @startDate IS NOT NULL AND @endDate is Not null
    Begin    
       set @strCondition = ' FO.Rf_Date  >= convert(datetime, ''' + Convert(varchar,@startDate,112) + ''') and  FO.Rf_Date<= convert(datetime, ''' + Convert(varchar,@endDate,112) + ''')'
    End

Alex K. · Accepted Answer · 2014-03-10 12:11:56Z

1

If they can be empty or null you need to also exclude the empty variables, E.g.

IF NULLIF(@startDate, '') IS NOT NULL AND NULLIF(@endDate, '') IS NOT NULL

answered Mar 10, 2014 at 12:11

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

M.Ali · Accepted Answer · 2014-03-10 13:31:32Z

You can also make use of ISDATE() Function along with CASE statement something like this..

SET @strCondition = N' SELECT * FROM TableName WHERE 1 = 1 '     --<-- 1 = 1 so you can append any more line starting with 'AND'
                   + CASE WHEN ISDATE(NULLIF(@startDate,'')) = 1  
                           THEN   N' AND FO.Rf_Date  >= convert(datetime, ''' + Convert(varchar,@startDate,112) + ''') '
                          ELSE N'' END
                   + CASE WHEN ISDATE(NULLIF(@endDate,'')) = 1                        
                           THEN  N' AND  FO.Rf_Date  <= convert(datetime, ''' + Convert(varchar,@endDate,112) + ''')'
                          ELSE N'' END

Note

ISDATE() function returns 0 if the passed parameter isnt date value, It also returns 0 is the passed value is NULL.

Collectives™ on Stack Overflow

creating dynamic query in variable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related