You would do that with view(s). The ENCRYPT / DECRYPT would be only within the view(s).
The users don't have to query anything different, as you can make the original table name a view name (indeed, users should only ever query from a view name, never from the real table name). SQL will treat the view the same as a table as far as queries go.
SQL also has masking capability, if you don't really need encryption but just need a way to prevent users from seeing certain columns / data.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".