我创建一个存储过程,而得到了一些参数,反过来这些参数发送到我从OPENROWSET调用另一个存储过程,但我得到了一些语法错误。
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
我不能来创建它,我得到的错误信息:
Msg 102, Level 15, State 1, Procedure UpdatePrevFYConfigData,
Line 38 Incorrect syntax near '+'.
......但如果我使用硬编码值,它的工作参数!
请帮忙!