SQL IN BODS SCRIPTING(Select Multi cols into Multi Variable)

dataservices job design
1

Hi Buddies/Experts,

I am writing a BODS Script and I have to include some SQL statements in it.

I want to extract multiple columns from a SQL Table and assign those to multiple variables defined locally in BODS Job.

I can issue one cast sql statement for each column that I want to retrieve . That will work. But all these columns are from same row. So if I issue one separate sql statements for each column then it will impact the performance.

I tried below options to retrieve multiple columns from sql in BODS script. but both options did not work.
sql(‘db’,select name into {$Lname},state into {$Lstate} from table where condition);

sql(‘db’,select {$Lname}=name,{$Lstate}=state from table where condition);

Any help would be appreciated.

Mike.ETLSDS (BOB member since 2018-06-01)

2

[Moderator Note: Moved from General Discussion to Data Integrator -> DI: Designer and Job Design]

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

When I do the below code I am able to get multiple columns. But now the challenge is to pass these fields into BODS variables. Because @name_sql is local to sql command and can’t use outside.

sql( ‘db’, 'declare @name_sql nchar(20), @state1_sql nchar(10); select @name_sql=name, @state1_sql=state from table where condition);

can I use ‘cast sql’ to get multiple columns from the table?

Mike.ETLSDS (BOB member since 2018-06-01)