See the email of Igor Tandetnik from 18-Dec-2005: "Vishal Kashyap" wrote > Is their any way we can write simple stored procedures or functions > in sqlite. If yes please do guide me I need this functionality in one > of my open source project.
Not in the usual sense, meaning some language that gets stored in the database itself together with the data. The only thing that comes somewhat close is a trigger. It is possible to create a poor man's stored procedure like this: create table sp_dosomething (param1 int, param2 char); create trigger sp_dosomething_impl instead of insert on sp_dosomething begin -- one or more sql statements possibly referring to -- new.param1 and new.param2 end; -- To invoke: insert into sp_dosomething values(1, 'hello'); Note that triggers are rather limited in what they can do. They are just a bunch of SQL statements, there is no control flow (loops, if then else, goto) beyond what little you can implement in pure SQL. They cannot return values, except indirectly by inserting or updating some table. SQLite does not support cascading triggers, so if your "stored procedure" manipulates some table to which regular triggers are attached (perhaps ensuring data integrity), those triggers won't run. SQLite supports custom functions - see sqlite3_create_function[16]. You write them in C (or any other language that has bindings to SQLite API) and you have to install them every time you open a DB handle with sqlite3_open, before you can refer to them in your SQL statements. They are not stored in the database file itself. Finally, SQLite prepared statements (sqlite_prepare) can be thought of as simple stored procedures defined in your program. Similar to custom functions, you can prepare a statement right after opening the database, then keep it around. Igor Tandetnik Ran On 3/24/06, Chethana, Rao (IE10) <[EMAIL PROTECTED]> wrote: > > Hi, > > Can you tell me how to create a stored procedure in an sqlite3 database > and use the same in a trigger? Please provide an example (as complete as > possible). In the stored procedure I need to execute few queries on some > tables. Can you tell me how to do that also? > > Any help is deeply appreciated. > > Best Regards, > > Chethana >