Query from a stored procedure

In some cases it is difficult to get data by means of one Select command. ObjectGears system enables to read data also from a stored procedure. This technique is quite simple. You have to just create a stored procedure meeting prerequisites stated hereinafter.

Procedure creates a temporary table ##tmp, that will be filled with data. ObjectGears system will call the precedure, read the data from this table and at the end drop this table.

Process of call creation

1. Create a stored procedure, that will create a temporary table called ##tmp. Get the result data in the procedure and fill this table with these data.

CREATE PROCEDURE procedure_name
AS
BEGIN
create table ##tmp
(
id int,
code varchar(100),
name varchar(100),
[enabled] bit
)

insert into ##tmp
SELECT id, code, name, [enabled] FROM mytable
END

2. In the ObjectGears system create a new query and it its SQL definition put the following command, where procedure_name is name of your stored procedure:

exec procedure_name

Passing logged-in user into a stored procedure

It is possible to use variable {{ loginuser.id }} in a query, that will return ID of the logged-in user. This value can be also passed to a stored procedure. It is possible to pass also other parameters in the same way.

Perform following changes in the stored procedure:

1) Add one input parameter to the stored procedure. Uložené proceduře přidejte jeden vstupní parametr. It can be mandatory or not depending on your way of using it:

CREATE PROCEDURE procedure_name
  @userId int = null
AS
BEGIN
create table ##tmp
(
  id int,
  code varchar(100),
  name varchar(100),
  [enabled] bit
)

insert into ##tmp
  SELECT id, code, name, [enabled]
    FROM mytable
    Where @userId is null or ( @userId is not null and @userId = userColumn)
END

2) Add a parameter into the stored procedure call:

exec procedure_name {{ loginuser.id }}