title: how to use openrowset to execute a stored procedure with parameters
body: <p>I'm creating a stored procedure which gets some parameters and in turn these parameters are sent to another stored procedure which I'm calling from openrowset but I'm getting some syntax errors.</p>
  
  <pre><code>CREATE PROCEDURE UpdatePrevFYConfigData 
      -- Add the parameters for the stored procedure here
  
          @startDate datetime,
          @endDate datetime,
          @productGroup varchar(8000) = 'All',
          @projectType varchar(500) = 'All',
          @businessUnit nvarchar(50) = 'All',
          @developmentLocation nvarchar(100) = 'All'
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
  declare @start varchar(50)
  declare @end varchar(50) 
  
  set @start = cast(@startDate as varchar(40))
  set @end = cast(@endDate as varchar(40))
  
      -- Insert statements for procedure here
  select round(avg(a.DeviationDeadline),2) as DeviationDeadline, 
         round(avg(a.DeviationDefinition),2) as DeviationDefinition,
         round(avg(a.DeviationRDCosts),2) as DeviationRDCosts,
         round(avg(a.FunctionsAdded) + avg(a.FunctionsDeleted),2) as NotRealizedFuncs, 
         round(avg(a.DeviationPM2000Aufwand),2) as DeviationPM200Aufwand,
         round(avg(b.Defect),2) as Defect
         into #tempTable 
  from openrowset('SQLNCLI', 
                  'Server=.\sqlexpress;Trusted_Connection=yes;',  
                  'SET NOCOUNT ON;SET FMTONLY OFF;EXEC [BSC_DB].dbo.SelectScorecardGraphData
                      '''+@start+''',
                      '''+@end+''',
                      '''+@productGroup+''',
                      '''+@projectType+''',
                      ''1'',
                      ''0'',
                      ''All'',
                      ''Current'',
                      '''+@businessUnit+''',
                      '''+@developmentLocation+'''
                  ') as a,
  
      openrowset('SQLNCLI', 'Server=.\sqlexpress;Trusted_Connection=yes;',  'SET NOCOUNT ON;SET FMTONLY OFF;EXEC [BSC_DB].dbo.GetSPCDefectDistributionData
  '''+cast(@startDate as varchar(40))+''',
  '''+cast(@endDate as varchar(40))+''',
  ''Defect'',
  '''+@projectType+''',
  '''+@productGroup+''',
  '''+@businessUnit+''',
  '''+@developmentLocation+'''') as b
  
  
  update dbo.EA_ProcessScorecard_Config_Tbl
  set EPC_Deviation = case EPC_Metric
      when 'PM200' then (select  DeviationDefinition from #tempTable)
      when 'PM300' then (select  DeviationDeadline from #tempTable)
      when 'Cost'  then (select  DeviationRDCosts from #tempTable)
      when 'PM150' then (select  DeviationPM200Aufwand from #tempTable)
      when 'Defect' then (select Defect from #tempTable)
      when 'Funcs' then (select NotRealizedFuncs from #tempTable)
  END
  where EPC_Description = 'PrevFY' and EPC_FYYear = '0'
  
  drop table #tempTable
  
  END 
  
  GO
  </code></pre>
  
  <p>I'm not able to create it and I get the error message:</p>
  
  <pre><code>Msg 102, Level 15, State 1, Procedure UpdatePrevFYConfigData, 
  Line 38 Incorrect syntax near '+'.
  </code></pre>
  
  <p>... but if I use hard coded values for the parameters it works!! </p>
  
  <p>Please help!</p>