Submitted by: sqlguru (sqlguru)

Duplicates CORE2079
Duplicates CORE2080

Firebird is based off the relational model, RDMS are based of the relational model, SQL is based off the relational model so why is it that CRUD based stored procedures still have sequential row-by-row processing instead of a set based solution?

SQL-92 defines table value constructors and SQL 2008 implemented it (although very poorly). The solution would be to have table types based of a table which could be CRUD against and used as OUTPUT parameters in stored procedures.

Here is an example:

CREATE TABLE members
(
mem_username VARCHAR(25) NOT NULL, mem_email VARCHAR(25), mem_password VARCHAR(25), PRIMARY KEY(mem_username)
);

CREATE STORED PROCEDURE create_members
@members members
AS
BEGIN
INSERT INTO members
SELECT mem_username, mem_email, mem_password FROM @members;
END

To use this stored procedure:
DECLARE @members members;
INSERT INTO @members ( ('user1', 'user1email', 'user1pass'), ('user2', 'user2email', 'user2pass'), ('user3', 'user3email', 'user3pass') );
EXECUTE create_members @members;

The syntax above is for SQL Server, but you get the point.

The .NET adapter for Firebird would somehow need to pass a DataTable into the stored procedure. I'm not sure how SQL Server 2008 and the SQLClient provider for .NET does it.