Purpose of Stored Procedure

Question

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

Dave Lewis · Answer

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?

Mathieu Drouin · Answer

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

Mathieu Drouin · Answer

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.

Dave Lewis · Answer

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.

Purpose of Stored Procedure

Top Replies