Maybe the idea Rare originally had for Perfect Dark could be realized here... obviously without Rare though. :P Can't really think of much in game use for snapping shots though that aren't a novelty or anything. Only idea I could have is like, an Eternal Darkness like game where the camera will (unbeknownst to you) take pics and when your sanity hits a certain point, hit you with a screamer depicting your own face. :P Though that would be a problem since when playing the DS it isn't all that normal to hold it in a position where the camera is directly aimed at your face.
Would be nice to have games now where you can use music stored on the SD Card for the in game soundtrack. Trackmania would have been awesome for that.
There was a cellphone version of Fatal Frame where it would superimpose ghosts onto photos as part of the gameplay. That's the only game I could think of where taking photos could be used in a non-ridiculous way.
BUT, like I said before, the camera could be used for augmented reality things, either homebrew or (less likely) professional. There are people working on apps like these for the iPhone and for some smartphones, so I don't see why this wouldn't work with the DSi. It's just a matter of someone using the technology for a fun purpose suited to the system. It would be very interesting to see.