sql-server-2008 - 如何使用openrowset执行带参数的存储过程

Question

我正在创建一个存储过程，它获取一些参数，然后将这些参数发送到另一个我从 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 '+'.

...但如果我对参数使用硬编码值，它会起作用!!

请帮忙!

Answer 1

OPENROWSET 和 OPENDATASOURCE 都应该仅用于访问外部数据，例如，快速和肮脏的解决方案，或者当无法配置永久链接服务器时。这些函数不提供链接服务器提供的所有功能。
OPENROWSET 和 OPENDATASOURCE 的参数不支持变量。它们必须指定为字符串文字。如果需要将变量作为参数传递给这些函数，则可以使用 EXEC 语句动态构造和执行包含这些变量的查询字符串。
类似于(未检查语法)

DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = 'SELECT *
FROM OPENROWSET(''SQLNCLI'',''server=.\sqlexpress;Trusted_Connection=yes'',''SET NOCOUNT ON;SET FMTONLY OFF;EXEC [BSC_DB].dbo.SelectScorecardGraphData ''''' + cast(@param1 as varchar(10)) + ''''',''' + cast(@param2 as varchar(n)) ''')'
EXEC @sqlCommand

等等...
希望有帮助。亲切的问候，
斯特凡