SQL Insert Statement returning Identity of record using OUTPUT statement

SQL Insert Statement returning Identity of record using OUTPUT statement

  

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. 

Hi, 

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.

A tricky solution could be (given the id is an autonumber):

Instead of:

SELECT @InsertTableVar.RecordId FROM @InsertTableVar;

Use:

SELECT MAX(id) FROM @InsertTableVar;

:



John Alvin Salamat wrote:

Hi, 

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.

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. 

  • This is a working example i tried. 
  • Add parameter and mark it as Expand Inline. 
  • Assign value to the parameter as @TableName



@Joost, i hope the OP finds his answer in your suggestion. 

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 "@"