break and combine statements in script

[addpharm]

I am trying to find an answer on how to break and combine statements in script. I am passing more than 600 numbers as one parameter to a stored procedure. Because this is too long and has been cut into two lines when I paste into the script window automatically. In the middle of the data loading, an error message stated:Error converting data type varchar to numeric. However, testing with 500 numbers in one line, it works just fine.

Your help will be much appreciated.

[adamdavi3s]

We would need to see how the string is created but you should be able to manipulate it and do the first 300 then the second 300 etc.... I am guessing the string is simply going over the max length for SQL (4000,8000 etc depending on version)

Is there nothing better you can do to avoid this situation though? sounds kind of awful to deal with!

[addpharm]

script:

exec SP '111,222,333,444, .......................9999999.'

the 600 numbers as one parameter are to be passed to the stored procedure. it couldn't be shortened unfortunately.

many thanks

[marcus_sommer · Accepted solution]

I think you could use a variable for this like:

let v = '111,222,';

let v = '$(v)' & '333';

let v = '$(v)' & '444';

...

just simplified here. Another method could be to load all these parameters into a table and then concatenating them to one value and again assign it to a variable.

- Marcus

[addpharm]

in VBA, it is easy to use like "-" and "&" to break the line:

exec SP '111,222,333 ' _

& '444 .....................99999999'

just wonder does the qlikview script provide the similar approach?

many thanks

[marcus_sommer]

AFAIK no - but both above mentioned variable-approaches will work.

- Marcus

[addpharm]

Thank you, Marcus