What component to use to execute a MSSQl script that uses variables

[felprie]

Hi, I need to execute a T-SQL script that has variables and then pass the resultset to a flow, where I need to further process it.I'm using Talend Cloud Data Management Platform(v7.0.1). I've been trying to use a TDBrow MSSQL component to execute the script and then connecting that component with a tlogrow to check if it worked, but i've been unsuccessful. I need to mention that the schema from the result set is going to change depending on the variables that I’ve set on the query.

Here is the query that I’m using:

       DECLARE @sqlString as varchar(max)

       DECLARE @p_schema_name as varchar(255)=”+context.MySchema”;

       DECLARE @p_table_name as varchar(255)=”+context.MyTable+”;

    SET @sqlString = ''

       select @p_table_name

    SELECT @sqlString = @sqlString +

      CASE DATA_TYPE

      WHEN 'int' THEN 'ISNULL(RTRIM(CONVERT(varchar(10),' + COLUMN_NAME + ')),'''')'

      WHEN 'datetime' THEN 'ISNULL(RTRIM(CONVERT(varchar(10),' + COLUMN_NAME + ',112)),'''')' 

      WHEN 'datetime2' THEN 'ISNULL(RTRIM(CONVERT(varchar(10),' + COLUMN_NAME + ',112)),'''')'

      WHEN 'date' THEN 'ISNULL(RTRIM(CONVERT(varchar(10),' + COLUMN_NAME + ',112)),'''')'

      WHEN 'bit' THEN 'ISNULL(RTRIM(CONVERT(varchar(1),' + COLUMN_NAME + ')),'''')' 

      WHEN 'decimal' THEN 'ISNULL(RTRIM(CONVERT(varchar('+ CONVERT(varchar(2),NUMERIC_PRECISION) +'),' + COLUMN_NAME + ')),'''')'

      ELSE 'ISNULL(RTRIM(' + COLUMN_NAME + '),'''')'

      END  + '+'

    FROM INFORMATION_SCHEMA.COLUMNS

       WHERE TABLE_SCHEMA = @p_schema_name and TABLE_NAME = @p_table_name

       select @sqlString                    

       DECLARE @sqlStatementString as varchar(max)

       SELECT @sqlStatementString='SELECT '+'ID'+',HASHBYTES(''MD5'','+ @sqlString+'''I'''+')'+' FROM '+@p_table_name

       select @sqlStatementString

       EXEC (@sqlStatementString)

I've tried a simpler case, of just having declared one variable and then doing a select @variable,with tDBRow but then I check the tlogrow its coming empty:

Can someone please point me out on how to achieve this.

Best regards,

[vapukov · Accepted solution]

just use for this tMSSQLInput component and define schema - p_table_name (String)

from tMSSQLInput main row to tFlowToIterate, must resolve You tasks

[Anonymous]

I confirm the proposal of vapukov
I have the same problem and i resolved it by using this method