Waar is de tijd, deel 2: vieze queries in SQL Server.

<cfquery name=”caller.songlist” datasource=”#request.mainDSN#”>
   SELECT album_id, album_title, album_year, album_thumb, album_image,
          album_number,
          publisher_name, albumtype_name, artist_name,
          song_id, len(song_text) as songtext,
          CASE left(song_title,charindex(‘ ‘,song_title)) 
             WHEN ‘The ‘ THEN right(song_title, len(song_title)-4)+’, The’
             WHEN ‘A ‘ THEN right(song_title, len(song_title)-2)+’, A’
             WHEN ‘An ‘ THEN right(song_title, len(song_title)-3)+’, An’
             WHEN ‘Le ‘ THEN right(song_title, len(song_title)-3)+’, Le’
             WHEN ‘La ‘ THEN right(song_title, len(song_title)-3)+’, La’
             WHEN ‘Un ‘ THEN right(song_title, len(song_title)-3)+’, Un’
             WHEN ‘Les ‘ THEN right(song_title, len(song_title)-4)+’, Les’
             ELSE 
               CASE left(song_title,2) 
                  WHEN ‘L”’ THEN right(song_title, len(song_title)-2)+’, L”’
                  ELSE song_title
               END
             END
             as song_title,
          sa_side, sa_track, sa_length, sa_mp3
     FROM tblAlbums, tblAlbumTypes, tblArtists, tblPublishers,
          tblSong_Album, tblSongs
    WHERE album_albumtype_id=albumtype_id AND
          album_artist_id=artist_id AND
          album_publisher_id=publisher_id AND
          sa_album_id=album_id AND
          sa_song_id=song_id AND
          <cfif isdefined(“caller.attributes.Zoekstring”) AND
                 len(trim(caller.attributes.zoekstring))>
             ( {fn lcase(song_title)} LIKE ‘%#caller.attributes.zoekstring#%’
                  OR
               {fn lcase(song_text)} LIKE ‘%#caller.attributes.zoekstring#%’
             )
          <cfelseif isdefined(“caller.attributes.let”)>
            CASE left(song_title,charindex(‘ ‘,song_title)) 
               WHEN ‘The ‘ THEN right(song_title, len(song_title)-4)+’, The’
               WHEN ‘A ‘ THEN right(song_title, len(song_title)-2)+’, A’
               WHEN ‘An ‘ THEN right(song_title, len(song_title)-3)+’, An’
               WHEN ‘Le ‘ THEN right(song_title, len(song_title)-3)+’, Le’
               WHEN ‘La ‘ THEN right(song_title, len(song_title)-3)+’, La’
               WHEN ‘Un ‘ THEN right(song_title, len(song_title)-3)+’, Un’
               WHEN ‘Les ‘ THEN right(song_title, len(song_title)-4)+’, Les’
               ELSE 
                 CASE left(song_title,2) 
                    WHEN ‘L”’ THEN right(song_title, len(song_title)-2)+’, L”’
                    ELSE song_title
                 END
               END
            LIKE ‘#caller.attributes.let#%’
          <cfelse>
            1=0
          </cfif>
 ORDER BY song_title, album_year, sa_side, sa_track
</cfquery>