Sql call with output fail with MS sql 2017
lazarus / 16-Databases

----------------------------------------------------------------------
SteenJorgensen · 2018-02-12 12:01

I have a problem with a long sql query that don't work.
It say "Cannot open a non-selected statement. But i have a output in
the last .Add.

DATA.QueryIL2.Close;
DATA.QueryIL2.SQL.Text := 'DECLARE @t TABLE (Myint1 int, Myint2 int)';
DATA.QueryIL2.SQL.Add(    'INSERT INTO @t Select distinct Myint1 ,
Myint2 from myTable');
DATA.QueryIL2.SQL.Add(    'Where Myint3 =''' + Myint3 + '''');
DATA.QueryIL2.SQL.Add(    'Select sum(CAST(Myint2 as int)) as MyOut
from @t ');
DATA.QueryIL2.SQL.SaveToFile('700.sql');
DATA.QueryIL2.Open;  

The Query works fine when i execute in the sql manager tool.

----------------------------------------------------------------------
bigeno · 2018-02-12 12:16

But INSERT is there, what you expect ?

----------------------------------------------------------------------
SteenJorgensen · 2018-02-12 12:45

I expect to get the MyOut from the last line...
The insert is to the tmp table. Then i select from tmp table.

----------------------------------------------------------------------
valdir.marcos · 2018-02-12 15:22

SteenJorgensen, are you trying to use Microsoft SQL Server Temporary
Tables or Table Variables?
https://www.mssqltips.com/sqlservertip/1556/differences-between-sql-
server-temporary-tables-and-table-variables/

Instead of using TSQLQuery component (SQLdb tabsheet), try using
TSQLScript component.

Another solution could be using your Temporary Tables or Table
Variables embeded in a Stored Procedure.
That way, you could select your data from a stored procedure using a
TSQLQuery component.

----------------------------------------------------------------------
Leledumbo · 2018-02-14 04:27

TSQLQuery was not designed to run multiple SQL queries at once, let
alone a combination of select (which requires .Open) and non-select
(requires .ExecSQL). If you need to run insert followed by select
whose returned rows are required, there's no other way but to run each
query on its own (insert + .ExecSQL followed by select + .Open).