Search code examples
sqlsql-serverrrscript

Won't able to Pass the Table name Dynamically using sp_execute_external_script using R Script


I create this query, This will work fine On SQL Server When am using this query in inside sp_execute_external_script using R Script I won't able to pass '+@TableName+' please check and Suggest some Solution

// This code Work fine in 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+'''')

// This code is Using R script Need Suggestion here

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

Solution

  • Could you try this.

    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