Purpose of Stored Procedure

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.

Thanking all  for the answer.

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

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?

My most common use case for SPs is emptying and reloading data into materialized views.

if you want to manipulate thousands rows and you dont want to load them into CDTs because it is resource intensive then a stored procedure is the way to go. I think it is a typical example.

if you want to manipulate thousands rows and you dont want to load them into CDTs because it is resource intensive then a stored procedure is the way to go. I think it is a typical example.