Church is a pretty unique playing environment, and very varied too, which means that you can actually use loads of effects or no effects depending on the context. If your church has a standard worship band line-up, then a lot of the more out there sounds aren't going to work, but an envelope filter can be great fun - try the Electro Harmonix Q-Tron.
I've used almost every effect imaginable in a church context - distortion, delay, reverb, chorus, flange, wah, etc. etc. often to provide ambient background music for readings, prayers etc. Bass is such a versatile instrument and can be used so well to set a mood for church stuff.
The E-Bow is a great tool for setting moods in church, especially on fretless. you can fade notes in, sustain them, and with a delay unit build whole chords and washes of sound. And it'll work for other things, not just church - chill out rooms in clubs, poetry reading, meditation, soundtracks etc...
There are no limitations beyond your imagination!
)
Steve