SqlServer查询Excel怎么把连接地址设为相对路径

[Industry松]

select *
from openrowset
(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=d:\zl\student.xls',
'select * from [info$]'
)

这里的d:\zl\student.xls是绝对路径，我想通过
declare &pathname
set &pathname='xxxxx'
然后在连接到database=&pathname
测试都不同过 ，望大神回复

[Cherise_huang]

声明变量后，应该用动态语句进行操作

[Industry松]

declare @pathname varchar(50) declare @orderStr varchar(500) set @pathname ='d:\Test\111.xls' set @orderStr ='select * from openrowset ('+ '''Microsoft.Jet.OLEDB.4.0'+''','+ '''Excel 8.0;Database=' +@pathname + ';IMEX=1'+''','' select * from [Sheet1$] '+ ''')' exec(@orderStr);

[Industry松]

感谢2#，结贴了

[Industry松]

感谢1# 呵呵 sorry

[Cherise_huang]

没事~互相学习