Skip to main content
F
faisalf0001
Participating Frequently
February 11, 2021
Question

Purpose of Stored Procedure

  • February 11, 2021
  • 7 replies
  • 2 views

Hi All,

Could someone please help on Stored Procedure used in Appian?

My main question is why do we need Stored Procedure in Appian? What is the main purpose behind using this?

In What scenario it can be preferred over views?

Thanks

Faisal

    7 replies

    J
    jesusa310
    Inspiring
    February 11, 2021

    There are some situations where it can be useful. In my opinion we should use every tool for what is best at. A stored procedure can provide better overall performance to do heavy process of data directly in the database rather than with a business process in Appian. 

    One example of stored procedure could be to refresh/manipulate a big volume of data in the database. I remember refreshing all the data of a database with data coming from another database. The team tried to use a business process for that and it took two hours to process all the data. Instead, we replaced it with a stored procedure.

    Whenever the number of rows to manipulate is big and includes logic on every row I would consider using a stored procedure. It should be the exception rather than the rule. The first choice is to use Appian direclty. If it is a special case that involves processing a lot of data then consider a stored procedure.

    mathieud0001
    mathieud0001
    Brainy
    February 11, 2021

    A stored procedure is not the same as a view. I used them mainly to refresh data tables in the database (i.e. truncate and refill with data). You could use the Query Database smart service to do this but I don't like the way that you can't parameterize the datasource name. I feel that the Stored Procedure is just easier from a deployment perspective.

    davel001150
    davel001150
    Inspiring
    February 11, 2021

    A VIEW is a stored SELECT statement that you run over and over.  Basically you SELECT things from a table JOIN to another table JOIN to another table and so on and so on, until you get something really useful that you like, that maps exactly to one of your CDTs for instance.  You save that SELECT you just built in a way it can be automatically run any time you want, and doesn't have to be rewritten from scratch every time.  That's a VIEW.  It lets you look at data.

    A STORED PROCEDURE allows you not only to look at data, but do practically ANYTHING you can possibly think of.  You can CREATE TABLE, ALTER TRIGGER, DROP INDEX, MODIFY USER, RENAME TABLE, REPLACE VIEW, DELETE DATABASE; basically ANYTHING.  Manipulate data, run calculations, put the results of calculations over here into a table over there, change the whole architecture of your schema, make a whole new database defined at runtime.  ANYTHING.  Is what you want to do even the slightest bit more complicated than writing a few rows to a single table?  Consider a STORED PROCEDURE.  Also, keep in mind that there are also FUNCTIONS, which are STORED PROCEDURES that return a value.

    F
    faisalf0001Author
    Participating Frequently
    February 11, 2021

    Thanking all  for the answer.

    My query is mainly as to why we use SP in Appian (Some Use cases will be helpful).

    davel001150
    davel001150
    Inspiring
    February 11, 2021

    If you need to write to three separate tables, and if any of the writes fail, you need to make sure that they all do.

    If you need to write to a table, then to another table with a FK reference to that table, then to a lookup table with a link to both the parent and the child of the last two steps, then save the ID from the lookup table to a fourth table, and who knows why you have such circular dependencies, but you need to store the ID from the row in the fourth table to the first table you wrote to, and only then commit.

    If you need to delete a user row from the database when that user has been deactivated for 6 mo. according to business rules.

    When you want to lock a row from being edited by multiple people who can access the same Related Action, as part of a pessimistic locking mechanism.

    When you need to do 15 calculations on the data before you write and you want to avoid race conditions.

    If for some unknown reason you need to ALTER TABLE at runtime.

    If you need to do something I can't think of right now.

    You'd use a stored procedure.

    It's a bit like asking "What would I do with Java?"  What wouldn't you do with Java?

      Terms & ConditionsAccessibility statement