Sorry to necro this thread, but I was looking for alternative solutions and came across this. Thought I'd throw my 2p in.

I've had this problem a lot recently. My solution was to create a single scalar UDF, with a CASE inside it, which I passed a constant name in to, ie:

@myValue = dbo.Constant ( 'asset' )

rather than the more verbose:

SELECT @myValue = Constants.Value FROM Constants WHERE Constants.Name='asset'

I don't like use of CLR for this to be honest and applying UDF in queries is imho a disaster for performance in many cases.