Skip to content
+1-888-319-3663

COMMUNITY FORUM

Cannot get SQL Statement result assigned to global variable

Mariano Gomez asked 7 years ago
I have been struggling to get the result of a SQL statement assigned to a global variable. My SQL statement is as follows:

DECLARE @ErrorCode INT, @PMTNUMBER VARCHAR(30);
EXEC taGetPMNextPaymentNumber 1, @PMTNUMBER OUTPUT, @ErrorCode;
SELECT @PMTNUMBER;

I have setup a global variable which I am using to get the content of @PMTNUMBER assigned to.

NOTE: I already have a GP Rolling Column defined in this map.

Where am I going wrong in all this?
Dominic Beland replied 3 years ago

I’m able to fetch SQL data in a calculated column.

With this: https://www.eonesolutions.com/tech-tuesday-query-data-in-smartconnect-with-net-scripting/

However I’m not able to assign the result to a variable.

How do you assign the result to the variable?

And Where do you assign the dummy value?

Is it in the global variable tab of the map or in a task that runs before the document?

Patrick Roth Staff replied 3 years ago

The issue from Dominic was that in the calculation script the code was like:

result = DataReader[0]
return result
GBL_MYRESULT= result

The problem is that the calculation (properly) returns the value on the return statement but the script ends at that point so GBL_MYRESULT never gets populated.

result = DataReader[0]
GBL_MYRESULT= result
return result

The easy solution is to reorder the script lines to return last.

Answers
Best Answer
Mariano Gomez answered 7 years ago
I figured this one out. Apparently, in order to get this to work, one needs to assign a dummy value to the global variable or nothing can get assigned by SmartConnect. This dummy value works as some sort of initialization so future values can be assigned.

If you would like to submit an answer or comment, please sign in to the eOne portal.