Four Stars

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

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:

tsql_script_variables.png

 

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

Best regards,

2 ACCEPTED SOLUTIONS

Accepted Solutions
Twelve Stars

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

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

 

from tMSSQLInput main row to tFlowToIterate, must resolve You tasks

-----------
Five Stars

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

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

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

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

 

from tMSSQLInput main row to tFlowToIterate, must resolve You tasks

-----------
Five Stars

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

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