run-time changeable queries

I need to extract and store a value from a table (or from a MS Access file with OpenDataSource) which is not always the same and it is therefore stored in the @.openfile variable. Something like this:
...
declare @.standardselect nvarchar(4000)
declare @.value int
select @.standardSelect='select top 1 @.value=val from ' + @.openfile
exec (@.standardSelect)
...

It obviously doesn't work because the variable @.value is not declared within the sql string.
However, since @.openfile is always different, I need to pass it through a string and the only way I know is within a variable. If I declare @.value inside the @.standardselect it is not accessible to the rest of the procedure, which is not acceptable for me.

Any suggestions?I have solved it using a temporary table, rather than a variable, where to store the value val. But if you have a better idea...|||

You can use EXEC statement

@.sSQL = 'select...from ' + @.tablename + ' where ...'
Exec @.sSQL

However this is a bad practice.

|||Andranik Khachatryan, I guess you haven't read the code in my first message?

Andranik Khachatryan wrote:

You can use EXEC statement

@.sSQL = 'select...from ' + @.tablename + ' where ...'
Exec @.sSQL

However this is a bad practice.

|||

Oops sorry :)

My bad, I am a bit careless today.

|||

You could use sp_executesql with output parameters to do this.

Declare @.StandardSelect nvarchar(4000)
Declare @.Value Int

Select @.StandardSelect = 'Select top 1 @.Value=Id From ' + @.OpenFile
exec sp_executesql @.StandardSelect, N'@.Value int output', @.value OUTPUT

Select @.value

|||

You don't really need dynamic SQL for doing this. You can do below instead:

declare @.source varchar(30)

-- ... initialize based on whether you want to query table or Access

set @.source =

declare @.value int

set @.value = (

select top 1 val from (

select val from your_table where @.source = 'table'

union all

select val from opendatasource(...) where @.source = 'access'

) as t

order by ...

)