Syntax error on extracting excel

Question

Create Procedure USP_UploadTraining
@filePath varchar(100) AS

BEGIN

Insert into Training_TRNS (EmpName,EmpJobFunction,EmpRegion,SpeCourse_ID,status,DueDate)
select F2+' '+F3 as Name,F4 as JobFunction, F6 as EVPRegion, F7 as CourseTitle,F8 as Status, convert(varchar,F9,101) as DateAcq
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database='@filePath'', 'SELECT * FROM [Sheet1$]')
where f2!='First Name'

END

it's give syntax error can anyone this modify.

Answer 1

you need to use Sp_ExecuteSql for this because you have to replace @filePath when you run the query

Try following query

 Create Procedure USP_UploadTraining
@filePath varchar(100) AS

BEGIN
DECLARE @ParmDefinition nvarchar(500);
SET @ParmDefinition = N'@filePath1 varchar(100)';
DECLARE @SQLString nvarchar(max);

SET  @SQLString='Insert into Training_TRNS (EmpName,EmpJobFunction,EmpRegion,SpeCourse_ID,status,DueDate)
select F2+'' ''+F3 as Name,F4 as JobFunction, F6 as EVPRegion, F7 as CourseTitle,F8 as Status, convert(varchar,F9,101) as DateAcq
FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database= @filePath'', ''SELECT * FROM [Sheet1$]'')
where f2!=''First Name'''


EXECUTE sp_executesql
     @SQLString
    ,@ParmDefinition
    ,@filePath1 = @filePath
    ;


END