...
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 @.sSQLHowever 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 ...
)
