Really nasty old code

[techskool]

I wrote a variable SQL statement, and im totally not happy with it, even though it works.

There must be a better way to write it, but how?

---------sql="SELECT * FROM stock"

If Artist <> &quot;&quot; Then
sql = sql + &quot; WHERE Artist LIKE '%&quot;&Artist&&quot;%'&quot;
End if

If Title <> &quot;&quot; Then
If Artist = &quot;&quot; Then
sql = sql + &quot; WHERE Title LIKE '%&quot;&Title&&quot;%'&quot;
Else
sql = sql + &quot; AND Title LIKE '%&quot;&Title&&quot;%'&quot;
End If
End If

If Label <> &quot;&quot; Then
If Artist = &quot;&quot; And Title = &quot;&quot; Then
sql = sql + &quot; WHERE Label LIKE '%&quot;&Label&&quot;%'&quot;
Else
sql = sql + &quot; AND Label LIKE '%&quot;&Label&&quot;%'&quot;
End If
End If

If YearMade <> &quot;&quot; Then
If Artist = &quot;&quot; And Title = &quot;&quot; And Label = &quot;&quot; Then
sql = sql + &quot; WHERE YearMade LIKE '%&quot;&YearMade&&quot;%'&quot;
Else
sql = sql + &quot; AND YearMade LIKE '%&quot;&YearMade&&quot;%'&quot;
End If
End If

If InStock <> &quot;&quot; Then
If Artist = &quot;&quot; And Title = &quot;&quot; And Label = &quot;&quot; And YearMade = &quot;&quot; Then
sql = sql + &quot; WHERE InStock LIKE '%&quot;&InStock&&quot;%'&quot;
Else
sql = sql + &quot; AND InStock LIKE '%&quot;&InStock&&quot;%'&quot;
End If
End If

If Audio <> &quot;&quot; Then
If Artist = &quot;&quot; And Title = &quot;&quot; And Label = &quot;&quot; And YearMade = &quot;&quot; And InStock = &quot;&quot; Then
sql = sql + &quot; WHERE Audio <> NULL &quot;
Else
sql = sql + &quot; AND Audio <> NULL &quot;
End If
End If

If Picture <> &quot;&quot; Then
If Artist = &quot;&quot; And Title = &quot;&quot; And Label = &quot;&quot; And YearMade = &quot;&quot; And InStock = &quot;&quot; And Audio = &quot;&quot; Then
sql = sql + &quot; WHERE Picture <> NULL &quot;
Else
sql = sql + &quot; AND Picture <> NULL &quot;
End If
End If

If CloseUp <> &quot;&quot; Then
If Artist = &quot;&quot; And Title = &quot;&quot; And Label = &quot;&quot; And YearMade = &quot;&quot; And InStock = &quot;&quot; And Audio = &quot;&quot; And Picture = &quot;&quot; Then
sql = sql + &quot; WHERE CloseUp <> NULL &quot;
Else
sql = sql + &quot; AND CloseUp <> NULL&quot;
End If
End If

sql = sql + &quot; ORDER BY Artist, Title, Label&quot;
End If

-----

Cheers

Dave

 

[martindavey]

Here's one option:-

sql = &quot;WHERE 1 = 1 &quot;
If Artist <> &quot;&quot; Then sql = sql & &quot;AND Artist LIKE '%&quot;&Artist&&quot;%' &quot;
If Title <> &quot;&quot; Then sql = sql & &quot;AND Title LIKE '%&quot;&Title&&quot;%' &quot;
If Label <> &quot;&quot; Then sql = sql & &quot;AND Label LIKE '%&quot;&Label&&quot;%' &quot;
If YearMade <> &quot;&quot; Then sql = sql & &quot;AND YearMade LIKE '%&quot;&YearMade&&quot;%' &quot;
If InStock <> &quot;&quot; Then sql = sql & &quot;AND InStock LIKE '%&quot;&InStock&&quot;%' &quot;
If Audio <> &quot;&quot; Then sql = sql & &quot;AND Audio <> NULL &quot;
If Picture <> &quot;&quot; Then sql = sql & &quot;AND Picture <> NULL &quot;
If CloseUp <> &quot;&quot; Then sql = sql & &quot;AND CloseUp <> NULL &quot;
sql = sql & &quot;ORDER BY Artist, Title, Label &quot;


Regards, Martin Davey.

 

[techskool]

many thanks 'martindavey'

wonder why id written such a long winded version now!

thanx

dave