如何使用openrowset执行带参数的存储过程 [英] how to use openrowset to execute a stored procedure with parameters

问题描述

我正在创建一个存储过程，该存储过程获取一些参数，然后将这些参数发送到另一个存储过程，该存储过程是我从openrowset调用的，但是遇到一些语法错误.

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.

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

我无法创建它，但收到错误消息:

I'm not able to create it and I get the error message:

Msg 102, Level 15, State 1, Procedure UpdatePrevFYConfigData, 
Line 38 Incorrect syntax near '+'.

...但是如果我使用硬编码的值作为参数，它将起作用！！

... but if I use hard coded values for the parameters it works!!

请帮助！

推荐答案

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

Both OPENROWSET and OPENDATASOURCE should be used only for accessing external data for, let's say, quick and dirty solutions, or when it is not possible to configure a permanent linked server. These functions do not provide all of the functionality available from a linked server. The arguments of OPENROWSET and OPENDATASOURCE do not support variables. They have to be specified as string-literal. If variables need to be passed in as arguments to these functions, a query string containing these variables can be constructed dynamically and executed using the EXEC statement. Similar to (not syntax checked)

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

以此类推... 希望能有所帮助.亲切的问候， 斯蒂芬

And so on... Hope that helps. Kind regards, Stefan

这篇关于如何使用openrowset执行带参数的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！