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>