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.
ALTER procedure sp_BulkInsert1
If you check BOL for the syntax of the BULK INSERT 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.
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)
RETURN Replace(@pcIn, '''', '''''')
Given 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.