CODEWITHSUNDEEP
sqlitestored-procedures
CrazyC
CrazyC

Reputation: 1896

Does SQLite support stored procedures?

I am evaluating the SQLite database for my requirement. Does SQLite support stored procedures?

If yes then what are the limitations? Does SQLite support the range?

Upvotes: 15

Views: 44870

Answers (4)

Bernardo Ramos
Bernardo Ramos

Reputation: 4577

No, but you can use this:

Stored Procedures for SQLite

Here is how to create a procedure:

CREATE OR REPLACE PROCEDURE add_new_sale(@products) BEGIN 
 SET @sale_id = INSERT INTO sales (time) VALUES (datetime('now')) RETURNING id;
 FOREACH @name, @qty, @price IN @products DO 
   INSERT INTO sale_items (sale_id, name, qty, price) VALUES (@sale_id, @name, @qty, @price);
 END LOOP;
 RETURN @sale_id;
END;

And how to call it:

CALL add_new_sale(ARRAY( ARRAY('DJI Avata',2,1168.00), ARRAY('iPhone 14',1,799.90) ));

Note: I am the creator

Upvotes: 0

ofcoursedude
ofcoursedude

Reputation: 456

If you really want to store SQL code in the DB (such as when you want to develop cross-platform apps), you can create a specific table that will store raw SQL commands that do the thing, then in the client you obtain the SQL command. e.g.

var myString = db.CreateCommand("SELECT SqlColumn FROM tablewithsqlcommands WHERE Procname=theprocedureIwant").ExecuteScalar();

and then execute it in the second step

var myResult = db.CreateCommand(myString).whatever_execution_method_you_need();

Upvotes: 9

Tony O'Hagan
Tony O'Hagan

Reputation: 22692

A key reason for having stored procs in a database is that you're executing SP code in the same process as the SQL engine. This makes sense for database engines designed to work as a network connected service but the imperative for SQLite is much less. Given that it run as a DLL in your current process it makes more sense to implement SP in the client language.

You can however extend SQLite with your own user defined functions in the host language (PHP, Python, Perl, C#, Javascript, Ruby etc). I've done this in C# using DevArt's SQLite to implement password hashing.

Upvotes: 11

Matthew Flaschen
Matthew Flaschen

Reputation: 284816

No, it does not. See Appropriate Uses For SQLite on the main site.

Upvotes: 15

Related Questions