Using BETWEEN in SQL CASE where clause

[Former Member]

I want a query something similar to the below condition . This does not work...how should I do?

WHERE
    tblMaterial.mat_type IN(1,3,7) AND

    CASE Len(@Mat2)
    WHEN 0 THEN
        tblMaterial.mat_nr = @Mat1
    ELSE
        tblMaterial.mat_nr Between @Mat1 And @Mat2
    END

ORDER BY
    tblMaterial.mat_nr

[justin_willey]

BETWEEN is a comparison operator, it doesn't assign values, so it's not clear what you want to happen when Len(@Mat2) is not 0.

Perhaps it would be best to explain how you want the WHERE clause to work.

[Former Member]

If @Mat2 has no value, should the condition be: tblMaterial.matnr = @Mat1. If there is any value in @Mat2 should the condition be: tblMaterial.matnr Between @Mat1 And @Mat2

[justin_willey]

I've deleted my code sample as it was incorrect - see Breck's / Volker's suggestion