Parameterized OPENROWSET Query
Posted on 2003-03-28
I am trying to write parameterized openrowset query for importing excel spreadsheets into a sql database. So far I have been able to achive this using a hard coded version. However, I really need the code to be dynamic so I require parameters. My attempt so far is as follows:
--CREATE PROCEDURE ImportTable
DECLARE @TableName AS VARCHAR(2000)
DECLARE @Path AS VARCHAR(2000)
DECLARE @Sheet AS VARCHAR(2000)
DECLARE @SQL AS VARCHAR(4000)
-- Variables Set for testing
SET @TableName = 'TEMP'
SET @Path= 'C:\Projects\Table1.xls'
SET @Sheet= 'Sheet1$'
SELECT * INTO ' + @TableName + ' FROM OPENROWSET (Microsoft.Jet.OLEDB.4.0 '','' Excel 8.0;Database=' + @Path + ' "," Select * FROM [' + @Sheet + '] )'
This query completes but does not run. Does anyone have any ideas as to why this is or if indeed it is possible to use parameters using the openrowset function.