Read TSQL variable from Temp Table I am hacking around trying to write a script to restore a backup from a known folder location.  The location will only ever contain one backup file.  I have come up with this so far, but I don't know how to get the value from the temp table into the restore database command.

CREATE TABLE #tempFileTable (FName VARCHAR(8000),Depth INTEGER, Files INTEGER)

INSERT INTO #tempFileTable
EXEC xp_dirtree 'S:\', 1, 1

SELECT TOP 1 FName FROM #tempFileTable

DROP TABLE #tempFileTable

DECLARE @DBName varchar(255) = 'DatabaseName'

RESTORE DATABASE @DBName FROM  DISK = ??? WITH  FILE = 1

GO
Maybe you could try something like : 

declare @file varchar(50)
declare @DBName varchar(50)
declare @sql varchar(200)

CREATE TABLE #tempFileTable (FName VARCHAR(8000),Depth INTEGER, Files INTEGER)

INSERT INTO #tempFileTable
EXEC xp_dirtree 'D:\', 1, 1

select @file = FName
from #tempFileTable

set @DBName = 'MyDB'

set @sql = 'RESTORE DATABASE '+@DBName+' FROM  DISK = ''D:\'+@file+''' WITH  FILE = 1'

print @sql