Bulk insert Variable Name

[DebbieChapman]

Does anybody know how i can send the file name to a stored proceedure.

I've written a stored proceedure which used bulk insert to inport the contents of an excel spreadsheet (debbie.xls for example)

i pick up the file name from within msAcces and can pass this in a variable to a stored proceedure. but when it then use variable in the stored proceedure, it doesnt read it

eg

CREATE PROCEDURE NCSImport @FileName VarChar(200)
AS

BEGIN

CREATE TABLE #NCSimport
(CPSO_Order Varchar(25),
System_Order_No Varchar(25))


BULK INSERT #NCSimport
FROM '@filename' <--this being the variable which contains the filename ie c:\debbie.xls
WITH (FIELDTERMINATOR = ',', TABLOCK)

can anyone help

 

[MDXer]

try this

CREATE PROCEDURE NCSImport @FileName VarChar(200)
@fileName varchar(100)
AS

DECLARE @strSQL varchar(5000)

BEGIN

Set @strSQL = 'CREATE TABLE #NCSimport '
Set @strSQL = @strSQL + '(CPSO_Order Varchar(25), '
Set @strSQL = @strSQL + ' System_Order_No Varchar(25))'
exec(@strSQL)

Set @strSQL = &quot;&quot;

Set @strSQL = 'BULK INSERT #NCSimport '
Set @strSQL = @strSQL + 'FROM ' + @filename
Set @strSQL = @strSQL + ' WITH (FIELDTERMINATOR = ',', TABLOCK)
exec(@strSQL)

&quot;Shoot Me! Shoot Me NOW!!!&quot;
- Daffy Duck