Incorrect syntax near @File

I am using this bulk insert command in procedure below. I am passing variable @.File inside of the procedure and I do not know the right syntax for it. Could you pls help me. When I enter the path for the file like 'C:\imp_file.csv' it works.

Thanks

ALTER procedure sp_BulkInsert1
@.File varchar(1000)

AS

BULK INSERT SQL_Tests.dbo.xRSA FROM @.File
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
CODEPAGE = 'RAW',
TABLOCK
)Because the syntax requires a constant, you can't use a variable... At least not directly anyway!ALTER procedure sp_BulkInsert1
@.File varchar(1000)
AS

EXECUTE ('BULK INSERT SQL_Tests.dbo.xRSA FROM ''' + @.File + '''
WITH
(
DATAFILETYPE = ''char''
, FIELDTERMINATOR = '',''
, ROWTERMINATOR = ''\n''
, CODEPAGE = ''RAW''
, TABLOCK
)' )

RETURN
GO-PatP|||:) Because the syntax requires a constant, you can't use a variable... At least not directly anyway!ALTER procedure sp_BulkInsert1
@.File varchar(1000)
AS

EXECUTE ('BULK INSERT SQL_Tests.dbo.xRSA FROM ''' + @.File + '''
WITH
(
DATAFILETYPE = ''char''
, FIELDTERMINATOR = '',''
, ROWTERMINATOR = ''\n''
, CODEPAGE = ''RAW''
, TABLOCK
)' )

RETURN
GO-PatP

Why do I need to use Execute Command?|||If you check BOL for the syntax of the BULK INSERT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_4fec.asp) command, you'll notice that the syntax requires a constant for the file name. The only way I know to make a variable appear as a constant is to execute it indirectly, via the EXECUTE statement. We're basically working around a limitation in the supoorted syntax.

-PatP|||Because it's dynamic sql...

Pat I can't get the injection in to the vien...maybe you can...I'm sure it can be done

USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(8000))
GO

CREATE PROC sp_BulkInsert1
@.File varchar(1000)
AS

EXECUTE ('BULK INSERT myTable99 FROM ''' + @.File + '''
WITH
(
DATAFILETYPE = ''char''
, FIELDTERMINATOR = '',''
, ROWTERMINATOR = ''\n''
, CODEPAGE = ''RAW''
, TABLOCK
)' )

RETURN
GO

DECLARE @.x varchar(1000)
SELECT @.x = 'c:\config.sys' + '''' + ' GO SELECT ' + '''' + 'Lets execute some damaging sql' + '''' + ' GO'
EXEC sp_BulkInsert1 @.x
GO

DROP PROC sp_BulkInsert1
DROP TABLE myTable99
GO|||I'd use one of my quote fixers. I'm having to shoot from the hip since my system is toast at the moment, but it goes something like:CREATE FUNCTION dbo.FixQuote(@.pcIn VARCHAR(8000)) RETURNS VARCHAR(8000)
BEGIN
RETURN Replace(@.pcIn, '''', ''')
ENDGiven that little function, you could wrap it around the parameter to inhibit code injection. Note that it is MUCH better to prevent the injection at the source (the client/middleware machine) rather than trying to inhibit it at SQL Server.

-PatP