无法使用 sp_execute_external_script R 脚本动态传递表名

Question

我创建此查询，这将在SQL Server上正常工作当我在内部使用此查询时sp_execute_external_script使用 R 脚本我无法通过"+@TableName+">请检查并提出一些解决方案

此代码在 SQL 中工作正常

Declare @TableName nvarchar(max)
Declare @Plant nvarchar(max) ='XXX'
Declare @Tub nvarchar(max) ='YYY'
Set @TableNameByModelName = 'Ref_curve' -- Need This from UI
                                exec (N'SELECT 
                                       p.[Plant]
                                      ,P.[Tub]
                                      ,r.[Power]    
                                  FROM [dbo].[Analysis_Curve] as P
                                  INNER join [P_Analysis].dbo.'+@TableName+'  AS r on r.Speed = P.Speed
                                  where P.Plant= '''+@Plant+'''  
                                  AND P.Tub = '''+@Tub+'''')

此代码在此处使用 R 脚本需要建议

Alter procedure [dbo].[Pass_Dynamic_TableName]   
    (            @Plant1 nvarchar(50),
                 @Tub1 nvarchar(50)
    )
As 
Begin
Declare @TableName NVARCHAR(200);
Set @TableName = 'Ref_curve'
EXEC sp_execute_external_script
@language =N'R',
@script=N'df <- as.data.frame(InputDataSet);
          OutputDataSet <-df'
              ,@input_data_1 =N'SELECT 
                                 p.[Plant]
                                ,P.[Tub]
                                ,r.[Power]  
            FROM [dbo].[Analysis_Curve] as P
            INNER join [P_Analysis].dbo.@TableName AS r on r.Speed = P.Speed
            where P.Plant= @Plant22
            AND P.Tub = @Tub22'              
        ,@params = N'@Plant22 varchar(50) OUTPUT, @Tub22 varchar(50) OUTPUT'
        ,@Plant22 = @Plant1
        ,@Tub22 =@Tub1
WITH RESULT SETS Undefined;
END

Answer 1

你能试试这个吗？

Alter procedure [dbo].[Pass_Dynamic_TableName]   
    (            @Plant1 nvarchar(50),
                 @Tub1 nvarchar(50)
    )
As 
Begin
Declare @TableName NVARCHAR(200);
Set @TableName = 'Ref_curve'
DECLARE @input_script NVARCHAR(MAX) =N'SELECT 
                                 p.[Plant]
                                ,P.[Tub]
                                ,r.[Power]  
            FROM [dbo].[Analysis_Curve] as P
            INNER join [P_Analysis].dbo.' + QUOTENAME(@TableName) + ' AS r on r.Speed = P.Speed
            where P.Plant= @Plant22
            AND P.Tub = @Tub22'   
EXEC sp_execute_external_script
@language =N'R',
@script=N'df <- as.data.frame(InputDataSet);
          OutputDataSet <-df'
        ,@input_data_1 = @input_script              
        ,@params = N'@Plant22 varchar(50), @Tub22 varchar(50) '
        ,@Plant22 = @Plant1
        ,@Tub22 =@Tub1
WITH RESULT SETS Undefined;
END