# Stored Procedure

**URL:** <https://discuss.elastic.co/t/stored-procedure/158519>\
**Category:** Elasticsearch\
**Created:** [November 28, 2018, 9:15am UTC](https://discuss.elastic.co/t/stored-procedure/158519 "2018-11-28T09:15:23Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Raghunadhan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raghunadhan/32/44239_2.png) [@Raghunadhan](https://discuss.elastic.co/u/Raghunadhan)\
**Post date:** [November 28, 2018, 9:15am UTC](https://discuss.elastic.co/t/stored-procedure/158519/1 "2018-11-28T09:15:24Z")

</div>

I want to stored procedure in elastic search as in sql  
For example in sql

stored procedure contains  
insert operations  
conditions  
insert operations based on conditions(like if condition).

Example of Stored Procedure

Create Procedure sp\_ADD\_USER\_EXTRANET\_CLIENT\_INDEX\_PHY  
(  
@ParLngId int output  
)  
as  
Begin  
SET @ParLngId = (Select top 1 ParLngId from T\_Param where ParStrNom = 'Extranet Client')  
if(@ParLngId = 0)  
begin  
Insert Into T\_Param values ('PHY', 'Extranet Client', Null, Null, 'T', 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL)  
SET @ParLngId = @@IDENTITY  
End  
Return @ParLngId  
End

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [November 28, 2018, 9:33am UTC](https://discuss.elastic.co/t/stored-procedure/158519/2 "2018-11-28T09:33:24Z")

</div>

There is no Stored Procedure in elasticsearch.

You can have a look at Node Ingest feature (Easier) or Logstash (more flexible) may be to solve what you want to solve.

Or could you explain what is the use case ?

---

<div class="post-metadata">

**Author:** ![Raghunadhan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raghunadhan/32/44239_2.png) [@Raghunadhan](https://discuss.elastic.co/u/Raghunadhan)\
**Post date:** [November 28, 2018, 9:39am UTC](https://discuss.elastic.co/t/stored-procedure/158519/3 "2018-11-28T09:39:14Z")

</div>

We are book publishing company.Need to generate reports and we process the book for various stages for publishing like spell check,artwork ,pagination etc

To keep track of the stages of book we developed a tool and using this tool we move from one stage to another stage i.e insert from table to another table that means we write various insert statement using conditions in stored procedures and generate report.Is elastic search is suitable this kind of business.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [November 28, 2018, 9:46am UTC](https://discuss.elastic.co/t/stored-procedure/158519/4 "2018-11-28T09:46:11Z")

</div>

So you want to record in elasticsearch the different steps of your process, right?  
You should may be index events like:

```auto
POST book-event/_doc
{
  "isbn": "XYZ",
  "date": "2018-11-26T10:00:00.000",
  "status": "spell check"
}

```

```auto
POST book-event/_doc
{
  "isbn": "XYZ",
  "date": "2018-11-26T11:00:00.000",
  "status": "artwork"
}

```

```auto
PUT book/_doc/XYZ
{
  "isbn": "XYZ",
  "lastUpdated": "2018-11-26T11:00:00.000",
  "dates": {
    "spellcheck": "2018-11-26T10:00:00.000",
    "artwork": "2018-11-26T11:00:00.000",
  },
  "status": "artwork"
}

```

And so on... With that I believe you can easily track what you need...  
For sure, you need to think about your design first. Before thinking of implementation details like the question you asked.

---

<div class="post-metadata">

**Author:** ![Raghunadhan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raghunadhan/32/44239_2.png) [@Raghunadhan](https://discuss.elastic.co/u/Raghunadhan)\
**Post date:** [November 28, 2018, 9:50am UTC](https://discuss.elastic.co/t/stored-procedure/158519/5 "2018-11-28T09:50:13Z")

</div>

SEE THE CONDITIONS IN BELOW SP and tell can we write like this

CREATE PROCEDURE `usp_AUSBWF_PageProof_Movement`(  
IN `par_schoolId` VARCHAR(20),  
IN `par_unitId` VARCHAR(20),  
IN `par_currentNodeId` SMALLINT,  
IN `par_processCycle` SMALLINT,  
IN `par_nextNodeId` SMALLINT,  
IN `par_nextMenuId` SMALLINT,  
IN `par_stageMove` VARCHAR(15),  
IN `par_processRemarks` VARCHAR(50),  
IN `par_userId` VARCHAR(50),  
IN `par_overTime` TINYINT UNSIGNED,  
IN `par_currStage` VARCHAR(15),  
IN `par_year_level` VARCHAR(50),  
IN `par_subject` VARCHAR(100)

)  
LANGUAGE SQL  
NOT DETERMINISTIC  
CONTAINS SQL  
SQL SECURITY DEFINER  
COMMENT ''  
BEGIN

DECLARE var\_nextProcessCycle SMALLINT;  
DECLARE var\_workDays INT;  
DECLARE var\_recDate DATETIME;  
DECLARE var\_dispDate DATETIME;  
DECLARE var\_deptDeadLine DATETIME;  
DECLARE var\_prvDeptDue DATETIME;  
DECLARE var\_percentage INT;  
DECLARE var\_workflow VARCHAR(50);  
DECLARE var\_currProcCyle INT;  
DECLARE var\_nextProcCyle INT;  
DECLARE var\_ProcCyle INT;  
DECLARE var\_OperatorCode VARCHAR (100);  
DECLARE var\_currentMenuId VARCHAR (100);  
DECLARE v\_recDate DATETIME;  
DECLARE v\_dueDate DATETIME;  
DECLARE v\_workDays INT ;  
DECLARE v\_prvDeptDue DATETIME;  
DECLARE v\_percentage INT;  
DECLARE v\_deptDeadLine DATETIME;  
DECLARE var\_UnitCnt INT;  
DECLARE var\_DatasetUnitCnt INT;

```
/ ***********************************START: Get TAT Percentage********************************************** /
SET v_recDate = (SELECT book_pp_received_date FROM tblausbook WHERE book_school_id=par_schoolId AND book_year_level=par_year_level AND book_subject=par_subject );
SET v_dueDate = (SELECT book_pp_due_date FROM tblausbook WHERE book_school_id=par_schoolId AND book_year_level=par_year_level AND book_subject=par_subject );
SET var_workflow=(SELECT book_workflow FROM tblausbook WHERE book_school_id=par_schoolId AND book_year_level=par_year_level AND book_subject=par_subject );

SET v_workDays = udf_AUSBWF_CompWorkdays (v_recDate,v_dueDate);   

SET v_prvDeptDue = NOW();

SET v_percentage = (SELECT percentage FROM tblaus_deadline WHERE stage='PageProof' AND serialNo=par_nextMenuId AND workflow=var_workflow);
SET v_deptDeadLine =udf_AUSBWF_DeadLineFun(v_percentage,v_recDate,v_dueDate,v_workDays);

SELECT MAX(process_cycle) INTO var_nextProcessCycle FROM tblausunitstatusdetails  
WHERE school_id = par_schoolId AND year_level=par_year_level AND subject=par_subject AND unit_id = par_unitId AND menu_serialno = par_nextMenuId;

SET var_nextProcessCycle = var_nextProcessCycle + 1 ;

IF var_nextProcessCycle IS NULL THEN
BEGIN
    SET var_nextProcessCycle = 1;
END;
END IF;

SELECT process_cycle, menu_serialno INTO var_currProcCyle, var_currentMenuId FROM tblausunitstatusdetails
WHERE school_id = par_schoolId AND year_level=par_year_level AND subject=par_subject AND unit_id = par_unitId AND node_id = par_currentNodeId AND process_in_dt IS NOT NULL AND process_out_dt IS NULL;

SELECT operator_id INTO var_OperatorCode FROM tbloperatorassignment
WHERE school_id = par_schoolId AND year_level=par_year_level AND subject=par_subject AND unit_id = par_unitId AND menu_serialno = var_currentMenuId AND process_cycle = var_currProcCyle;

```

/ **START: Movement** \*\*\*\*\*\*\*/

IF par\_currStage = 'PageProof' THEN  
BEGIN

```
    IF(var_currentMenuId=14 AND par_nextMenuId=14) THEN
    BEGIN

        UPDATE tblausunitstatusdetails SET process_out_dt = NOW(), over_time = par_overTime, user_id = var_OperatorCode, Track_User_Id = par_userId, Movement_remarks = 'From PMS'
        WHERE school_id = par_schoolId AND unit_id = par_unitId AND year_level=par_year_level AND subject=par_subject AND node_id = par_currentNodeId AND process_out_dt IS NULL;
        
        SET var_UnitCnt =(SELECT COUNT(unitd_id) FROM tblausunit_details WHERE unitd_school_id=par_schoolId and unitd_year_level=par_year_level and unitd_subject=par_subject);
        SET var_DatasetUnitCnt=(SELECT COUNT(unit_id) FROM tblausunitstatusdetails where school_id=par_schoolId AND year_level=par_year_level and subject=par_subject and menu_serialno='14' and process_out_dt is not null and process_cycle in( SELECT max(process_cycle) FROM tblausunitstatusdetails where school_id=par_schoolId AND year_level=par_year_level and subject=par_subject and menu_serialno='14' and process_out_dt is not null group by school_id,year_level,subject));
        
        IF(var_UnitCnt=var_DatasetUnitCnt) THEN
        BEGIN
        UPDATE tblausbook SET book_pp_despatch_date=NOW() WHERE book_school_id=par_schoolId AND book_year_level=par_year_level AND book_subject=par_subject ;
        END;
        END IF;
    END;

```

END;  
END IF;

END

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [November 28, 2018, 9:57am UTC](https://discuss.elastic.co/t/stored-procedure/158519/6 "2018-11-28T09:57:03Z")

</div>

I don't read SQL code sorry. Explain in clear english the use case not the technical implementation you have today.

I mean that moving from a SQL relational model to a NoSQL Object oriented model requires some thinking. I'd not try to mimic one system in the other.

---

<div class="post-metadata">

**Author:** ![Raghunadhan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raghunadhan/32/44239_2.png) [@Raghunadhan](https://discuss.elastic.co/u/Raghunadhan)\
**Post date:** [November 28, 2018, 10:07am UTC](https://discuss.elastic.co/t/stored-procedure/158519/7 "2018-11-28T10:07:07Z")

</div>

We had tables for every stages of book to keep track

For example If we complete the book process in artwork and next stage is pagination  
then we move the book datas to pagination table from artwork table based on conditions.

we will code all this functions in a single name stored procedures.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [November 28, 2018, 10:33am UTC](https://discuss.elastic.co/t/stored-procedure/158519/8 "2018-11-28T10:33:09Z")

</div>

That's similar to the model I described earlier with an example.

---

<div class="post-metadata">

**Author:** ![Raghunadhan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raghunadhan/32/44239_2.png) [@Raghunadhan](https://discuss.elastic.co/u/Raghunadhan)\
**Post date:** [November 28, 2018, 10:40am UTC](https://discuss.elastic.co/t/stored-procedure/158519/9 "2018-11-28T10:40:06Z")

</div>

OK ,Thank you for your help.

I will come back with details explanation sir

---

<div class="post-metadata">

**Author:** ![Raghunadhan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raghunadhan/32/44239_2.png) [@Raghunadhan](https://discuss.elastic.co/u/Raghunadhan)\
**Post date:** [December 11, 2018, 5:35am UTC](https://discuss.elastic.co/t/stored-procedure/158519/10 "2018-12-11T05:35:52Z")

</div>

Dear all,

We use term and also keyword while using aggregation query what does term and keyword mean in elastisearch

See below example  
GET /bank/\_search  
{  
"size": 0,  
"aggs": {  
"group\_by\_state": {  
"terms": {  
"field": "state.keyword"  
},  
"aggs": {  
"average\_balance": {  
"avg": {  
"field": "balance"  
}  
}  
}  
}  
}  
}

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [December 25, 2018, 5:58am UTC](https://discuss.elastic.co/t/stored-procedure/158519/12 "2018-12-25T05:58:19Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.