is there any way to expand a variable in line in a select statement without having to do the whole dang thing in dynamic sql? what I would like is:

declare @database varchar(32), @field char(20)
set @database = 'mydatabase'

select @field = myfield from @database .dbo.sometable

but of course- this doesn't work - I can do it in dynamic SQL, but then the var @field isn't avaialble in the calling procedure.

Is there any way just to imbed the variable in the select statement or otherwise pull this off without having to go to temp tables?

Thanks,
Ken