CODEWITHSUNDEEP
sqlsql-serverexcelstored-procedures
Ahmed Khalil
Ahmed Khalil

Reputation: 85

Issue in passing parameter on stored procedure

I have created a procedure without any problem. I have tested before creating the procedure but when I try to execute my procedure I have a problem only in the first parameter I have

SQL SERVER 2008 SHOW ME Error syntax in 'Mundus' 

ProcAvancementTotalbyEtab 'Erasmus Mundus','C:\Users\AA\Desktop\Table1.xlsx'

and After I try to make like this:

ProcAvancementTotalbyEtab N'Erasmus Mundus','C:\Users\AA\Desktop\Table1.xlsx'

This is my stored procedure:

alter proc ProcAvancementbyEtab
(

@nameEtabb  nvarchar(50),
@FilePath nvarchar(60)
)

as
begin
 EXEC
 (
 '
 SET NOCOUNT OFF;
 insert into Avancement(nameEtab)
SELECT distinct T.[EFP]
From OPENROWSET(''MICROSOFT.ACE.OLEDB.12.0'',
''Excel 12.0;Database='+@FilePath+''',
''SELECT *
 FROM [Sheet1$]'') T

  where T.[EFP] is not null  and T.[Avancement] is not null
 and 
not exists (select * from Avancement where 
Avancement.nameEtab=T.[EFP])
and T.[EFP]='+@nameEtabb+'
 ')
end

When I comment this line and T.[EFP]='+@nameEtabb+' it works fine so my probleme is here 

and T.[EFP]='+@nameEtabb+'

and when I test without stored procedure it work also OMG.

Upvotes: 1

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

My guess is that name is stored as a string. You need an extra pair of quotes:

and T.[EFP] = '''+@nameEtabb+'''

Upvotes: 1

Related Questions