Try something like this:

declare
@database varchar(32)
,@field char(20)
,@sql varchar(1000)

create table #Results (field1 as char(20))

set @database = 'mydatabase'

set @sql = 'insert #Results select myfield from '+@database+'.dbo.sometable'

exec( @sql )

select @field = myfield from #Results

BOb


>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