SQL Query: Assignment of value of an input variable in to internal temporary variable

[Eforenkow_PMI]

Spoiler

SQL Query: Assignment variable

Hello all !!!!

I have problems with assignment of value of an input variable to temporary variable. I create service with SQL Query. Added an input variable [[container]] with type of data a string. In assignment attempt: set @Container=[[container]] - the compiler ceases to highlight syntax and execution happens to an error. At execution of any command as soon as I insert an input variable, [[.....]] check of syntax shows that it is not right.

Whether somebody knows in what an error? In SQL Managment studio everything works.

[PaiChung]

My best guess is that the compiler within composer isn't set up to recognize that syntax as a possible parameter substitution.

You can try the more dangerous and more powerful << >> which is a string substitution and shouldn't cause any complaints but exposes you to potential SQL Injection

[Eforenkow_PMI]

You suggest to write set @container=<<container>>; instead of
set @container=[[Container]] ?

[PaiChung]

Correct!

For input parameters into a service standard when you bring it in it will be [[ ]] which is Parameter substitution but << >> is String substitution, so potentially the service is more 'forgiving'

If it still doesn't accept that, then you may have to go the full 'build your query as a string' route and then execute it with <<YourFullSQLStringHere>> approach

Here is an article on it: https://community.ptc.com/t5/ThingWorx-Developers/How-can-I-execute-a-sql-query-constructed-dynamically/m-p/501798

[Eforenkow_PMI]

I tried to do so 

IF  <<Container>> is not null

The compiler returned the message "Unable to Invoke Service GetEquipment MSSQL : null"

Tried so:

<<IF  [[Container]] is not null>>

The compiler returned the message"Unable to Invoke Service GetEquipment on MSSQL : Execute Query failed: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '<'."

Also I tried to conclude all query code in <<>> - the compiler return the same message

[PaiChung]

Did you try 

set @Container=<<container>>

Else you need to build the whole SQL as a STRING first in a separate JavaScript service

and then supply that full string as an input parameter to your SQL Query/Command

and then do <<MyInputParameterSQLString>>

[slangley]

Hi @Eforenkow_PMI.

If one of the solutions recommended by @PaiChung helped to solve your issue, please mark it as the Accepted Solution for the benefit of others who may have the same question.

Regards.

--Sharon