TM1 Forum

Hi all,
I'm looking for some advice on executing a SQL stored procedure from within a TI.
Here's what I've done.
I have a parameter ( pLoadNumber ) which is Numeric and the value will always change but for this example lets say the value is 100.0000
I need to pass this parameter to a stored procedure and I am using the ODBCOutput function.
The column Load_ID in the SQL table is stored as an int.

## EPILOG ##
sSP = 'EXEC [TM1ODBC].[data].[start_end_time] @load_id = Select pLoadNumber';
OdbcOutput ( cODBC, sSP );

# Close connection to ODBC source
OdbcClose ( cODBC );

I'm getting an error that says "Error converting datatype NVARCHAR to int" and I'm unsure how to amend the SQL statement to Convert or Cast to an int value.
Any help would be appreciated!
Cheers

CRP0021 wrote: ↑Wed Feb 26, 2020 3:07 am
## EPILOG ##
sSP = 'EXEC [TM1ODBC].[data].[start_end_time] @load_id = Select pLoadNumber';

Hi,

there is no parameter in your query - the complete query is in quotation marks.

try this:
sSP = 'EXEC [TM1ODBC].[data].[start_end_time] @load_id = Select ' | pLoadNumber;

i'm not that used to sql at the moment but to you really need the "select" for @load_id. firt thought was
if pLoadNumber is eg. a 9 the parameter for @load_id is "select 9" and that is obviously a string

maybe this

sSP = 'EXEC [TM1ODBC].[data].[start_end_time] @load_id =' | pLoadNumber;

works propper

best regards
orlando