Cursor Not Returned Query if Execute SQL Statement with Return Value

Question

I have an issue when executing query bellow with DBExpress and Delphi XE. I need to get last identity id from executed query :

function TServerDBUtils.ExecuteQueryWithIdentity(ASQLConn: TSQLConnection): Integer;
var
  newSQLQuery: TSQLQuery;
begin
  Result := -1;  
  newSQLQuery := TSQLQuery.Create(nil);
  try
    with newSQLQuery do
    begin
      SQLConnection := ASQLConn;

      SQL.Clear;
      SQL.Add('Insert into SampleTable(uomname) values(' + Quotedstr('bag') +')';

      SQL.Add('Select Scope_Identity()');
      Open;
      Result:= Fields[0].AsInteger;     
    end;
  finally
    FreeAndNil(newSQLQuery);
  end;
end;

I get error "cursor not returned query". I have used same method before, using FireDac & Delphi XE5 and got no error. No, I'm wondering if "open" is not allowed to do such thing in DBExpress. What method I should use? (We have to use DBExpress in our project) I've tried this :

function TServerDBUtils.ExecuteQueryWithIdentity(ASQLConn: TSQLConnection): Integer;
var
  newSQLQuery: TSQLQuery;
begin
  Result := -1;  
  newSQLQuery := TSQLQuery.Create(nil);
  try
    with newSQLQuery do
    begin
      SQLConnection := ASQLConn;

      SQL.Clear;
      SQL.Add('Insert into SampleTable(uomname) values(' + Quotedstr('bag') +')';
      ExecSQL;

      SQL.Clear;
      SQL.Add('Select Scope_Identity()');
      Open;
      Result:= Fields[0].AsInteger;     
    end;
  finally
    FreeAndNil(newSQLQuery);
  end;
end;

And always got null values, maybe because different session. Sorry for my bad english, and thanks in advance for any help.

Update : It works if we used @@identity :

SQL.Add('Insert into SampleTable(uomname) values(' + Quotedstr('bag') +')';
ExecSQL;

SQL.Clear;
SQL.Add('Select @@Identity');
Open;
Result:= Fields[0].AsInteger; 

But, there's problem as SQLServer told, that if there was a trigger on that table fired (on Insert), The return value is the last ID of table that trigger inserted.

Answer 1

The most elegant way on SQL-Server might be to use the OUTPUT Clause which is not only capable to return one ID but all new genered one in case of a multipart insert.

INSERT into aTable (aField)
OUTPUT Inserted.ID 
Values ('SomeValue')

If you have got a trigger on your table you will have to define a destination table for your OUTPUT

DECLARE @tmp table (ID int)
INSERT into aTable (aField)
OUTPUT Inserted.ID into @tmp 
Values ('SomeValue')
Select * from @tmp

Another advice would be to use parameters instead of hard coded values.

With TSQLQuery adding SET NOCOUNT ON before the statement will prevent the cursor not returned query error an deliver the expected result:

begin
  SQLQuery1.SQL.text :='SET NOCOUNT ON'
                +#13#10'DECLARE @tmp table (ID int)'
                +#13#10'INSERT into aTable (aField)'
                +#13#10'OUTPUT Inserted.ID into @tmp'
                +#13#10'Values (:P)'
                +#13#10'Select * from @tmp';
  SQLQuery1.Params.ParamByName('P').Value := 'SomeText';
  SQLQuery1.Open;
  Showmessage(SQLQuery1.Fields[0].asString);
end;