Use variable for datasource in Opendatasource

[lonestarprogramming]

I would like to create a stored procedure that imports an Excel file to
a sql server table. I need to be able to pass the path to the
spreadsheet to the stored procedure like so:

CREATE PROCEDURE dbo.ImportSpreadsheet

(
@Path nvarchar(120)

)

AS

SELECT * INTO Audit
FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0',
'Data Source = ' + @Path + ';Extended Properties=Excel 8.0')...Audit$
I will call the stored procedure from within my .NET application and
pass it the path as @Path. The path will be chosen by the user at run
time and will be something similar to
C:\Spreadsheets\Audits.xls.

I can make this run in the Query Analyzer:
SELECT * INTO Audit
FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0',
'Data Source = ''C:\Spreadsheets\Audits.xls\Audit_TC.xls'';Extend ed
Properties=Excel 8.0')...Audit$

I could run the SQL directly from my app, but I like to use stored
procs whenever I can for the speed and flexibility. I can change the
stored proc and not have to recompile/reinstall the app.

Any help would be greatly appreciated.

[Erland Sommarskog]

(lo*****************@gmail.com) writes:

I can make this run in the Query Analyzer:
SELECT * INTO Audit
FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0',
'Data Source = ''C:\Spreadsheets\Audits.xls\Audit_TC.xls'';Extend ed
Properties=Excel 8.0')...Audit$

I could run the SQL directly from my app, but I like to use stored
procs whenever I can for the speed and flexibility. I can change the
stored proc and not have to recompile/reinstall the app.

To do this in a stored procedure, you would have to use dynamic SQL.
See http://www.sommarskog.se/dynamic_sql.html for details.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx