如何使用 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 '+'.

但如果我使用硬编码参数的值，它就可以工作！

请帮帮我！

- Viniez

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

希望这能有所帮助。祝好！ 斯特凡

- ExternalUse

---

如果使用OPENROWSET调用存储过程的目的是将结果放入SELECT语句中，那么这样做就没有意义。我猜这是不可能实现的，被调用的存储过程需要改成函数。这是我最终采取的解决方案。 - Neo

Answer 2

    -- FOR USING OPENROWSETS
    EXEC sp_configure 'Ad Hoc Distributed Queries'
        ,1

    RECONFIGURE

    DECLARE @SQL NVARCHAR(MAX)

    SET @SQL = 'INSERT INTO #TABLESIZESYEAR SELECT NULL AS [TABLE NAME], * FROM OPENROWSET 
                    (''SQLOLEDB'',''Server=(local);TRUSTED_CONNECTION=YES;'',''set fmtonly off EXEC one.[dbo].[InvestigateDataGrowthByYearAndClient] @pDATECOLUMN =' + @YEARCOLUMN + ' ,
                        @pTABLENAME = ' + @TABLENAME + ' WITH RESULT SETS(
                        ([YEAR NAME] NVARCHAR(5) NULL
                                , [NUMBER OF ROWS] CHAR(11)    
                                , [RESERVED SPACE] VARCHAR(18) 
                                , [DATA SPACE] VARCHAR(18)    
                                , [INDEX SIZE] VARCHAR(18)    
                                , [UNUSED SPACE] VARCHAR(18) )
                    )
                        ;'') '

    DECLARE @ParmDefinition NVARCHAR(500) = '@pDATECOLUMN NVARCHAR(20)
                            ,@YEARCOLUMN NVARCHAR(20)
                            ,@pTABLENAME NVARCHAR(60)';

    EXECUTE sp_executesql @sql
        ,@ParmDefinition
        ,@YEARCOLUMN = @YEARCOLUMN
        ,@pDATECOLUMN = @YEARCOLUMN
        ,@pTABLENAME = @TABLENAME

- ProblemSolver