asked on

dynamic sql not making the right where clause statement

Hi,
I'm still working on converting the query to dynamic SQL. The DB is SQL Server 2005. In this particular part of the query there are two parameters passed in for the begining and end dates. Below is the statement that worked. This part is being converted simply because some other parts of the code require it.

Original code that worked:

AND cast(CASE ISDATE(RTRIM(LTRIM(MAT1_01_07))) WHEN 0 THEN '1/1/1901' ELSE RTRIM(LTRIM(MAT1_01_07))

END as datetime) BETWEEN @StatusDateBegin AND @StatusDateEnd

--------------------------
Below is the dynamic conversion code of above:

-------------------------Status Date Solution------------------
SET @StatusDates = ' AND cast(CASE ISDATE(RTRIM(LTRIM(StatusDate))) '
      + ' WHEN 0 THEN ' + '''1/1/1901'''
      + ' ELSE RTRIM(LTRIM(StatusDate)) END as datetime) '
      + ' BETWEEN ' + @StatusDateBegin + ' AND ' + @StatusDateEnd
SET @SQL_WHERE = @SQL_WHERE + @StatusDates

--------------------
Below is the actual part of the where clause that is built. This is should be much more complete:

AND cast(CASE

Thanks,

ASKER CERTIFIED SOLUTION

imitchie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

imitchie

I assume @StatusDateEnd and @StatusDateEnd are datetimes

awking00

I also assume that StatusDate is also a datetime, so it won't look anything like the "MAT1_01_07" string that worked. I think you need to cast it as a character string before applying the trim functions to it (and may also require some further manipulation if it needs to look like MAT1_01_07).

FairSkies

ASKER

Hi,
Thanks for the help. StatusDate is datetime in the view and the input parameters @StatusDateEnd are @ServiceDateBegin are are both datetime.

FairSkies

ASKER

I am still a little confused about the escaping the '. It doesn't quite seem consistient to me