Hi,
I've searched the forums already, but haven't found an answer.
I'm creating a SQL which inserts into a table (dynamically, via input parameter @EntityName).
I would like to return the Id of the record inserted. I do this via the line:
OUTPUT INSERTED.Id INTO @InsertTableVar
PROBLEM: How can I escape the @InsertTableVar as being part of the SQL statement? OutSystems thinks that @InsertTableVar is an input parameter... but it is really just part of the SQL text.
SyntaxEditor Code Snippet
DECLARE @InsertTableVar TABLE(RecordId INT); INSERT {@EntityName} ( {@EntityName}.[Name], {@EntityName}.[Description], {@EntityName}.[Order], {@EntityName}.[Is_Active] ) OUTPUT INSERTED.Id INTO @InsertTableVar VALUES (@Name, @Description, @Order, @Is_Active); SELECT @InsertTableVar.RecordId FROM @InsertTableVar;
Besides the why on earth would you want to do something like this in outsystems.
Will expandinline set on true not work and giving @insetwhatver as inout?
An SQL query with 6 steps, each step is a small query and holds the result in a temporary table. At the end of the script, all the 6 tables are joined together to return a result.
Why am i doing this in Outsystems? Because the data is in Outsystems and the output of the query needs to be put in a spreadsheet and emailed.
Using expand inline should do the trick to making it a literal and for SQL to recognize it as table variable. Use this thread as reference.
Hope this helps.
John Alvin Salamat wrote:
Thanks for the response.
John's suggestion took me notice the Test inputs tab. I then noticed the result in Executed SQL tab. That is where the clue was.
@Joost, i hope the OP finds his answer in your suggestion.
A tricky solution could be (given the id is an autonumber):
Instead of:
SELECT @InsertTableVar.RecordId FROM @InsertTableVar;
Use:
SELECT MAX(id) FROM @InsertTableVar;
:
By the way, the above method works fine when you Test the SQL. Executing in the flow is giving me this error -
Error in advanced query SQL1 in Preparation in SendAttachment in Email in Employees (declare @TempTable table(id bigint); insert into @TempTable (id) select id from {Employee} where id = 2; select * from @TempTable;): Incorrect syntax near the keyword 'table'.Incorrect syntax near ';'.
Was the right value passed to the parameter TempTable inside SendAttachment action? Make sure it has prefix "@"