Microsoft T-SQL supports a language feature called table-valued parameter (TVP), which is a parameter of a table type that can be passed to a stored procedure or function. For example, you may write: CREATE TYPE u_number_table AS TABLE (column_value INTEGER); CREATE FUNCTION f_cross_multiply ( @numbers u_number_table READONLY ) RETURNS @result TABLE ( i1 INTEGER, i2 … Continue reading How to Pass a Table Valued Parameter to a T-SQL Function with jOOQ
Tag: T-SQL
The Best Way to Call Stored Procedures from Java: With jOOQ
jOOQ is mainly known for its powerful type safe, embedded, dynamic SQL capabilities that are made available through code generation. However, a secondary use case of code generation is to use it for stored procedures (possibly exclusively for stored procedures). Stored procedures are powerful ways of moving complex data processing logic to the server. This … Continue reading The Best Way to Call Stored Procedures from Java: With jOOQ
Translating Stored Procedures Between Dialects
In the past years, we've invested a lot of effort into improving our procedural language capabilities in jOOQ. What started with a simple internal API to support the emulations of DDL clauses like these: -- Some dialect that supports this create table if not exists t (i varchar(10)); -- Db2 begin declare continue handler for … Continue reading Translating Stored Procedures Between Dialects
How to Emulate Partial Indexes in Oracle
A very interesting feature of the SQL Server and PostgreSQL databases (and some others, including SQLite) is the partial index (sometimes also called "filtered index"). That's an index that contains only "parts" of the table data. For instance, we can write the following index in SQL Server and PostgreSQL: CREATE INDEX i ON message WHERE … Continue reading How to Emulate Partial Indexes in Oracle
jOOQ 3.10 Supports SQL Server’s Table Valued Parameters
SQL Server has this nice feature called table-valued parameters (TVP), where users can pass table variables to a stored procedure for bulk data processing. This is particularly nice when the stored procedure is an inline table valued function, i.e. a function that returns a table as well. For instance: CREATE TYPE numbers AS TABLE (i … Continue reading jOOQ 3.10 Supports SQL Server’s Table Valued Parameters
jOOQ Newsletter: April 2, 2014
subscribe to this newsletter here Tweet of the Day Our customers, users, and followers are sharing their love for jOOQ to the world. Here are: Arturo Tena who simply loves jOOQ 3.3, and expresses this with a creative transformation of our version numbering scheme: https://twitter.com/arturotena/status/434502197217202176 Florin T.Pătraşcu who cannot stop integrating jOOQ with MicroMVC, because he discovers more … Continue reading jOOQ Newsletter: April 2, 2014
Add LATERAL Joins or CROSS APPLY to Your SQL Tool Chain
The T-SQL dialect has known the powerful CROSS APPLY and OUTER APPLY JOIN syntaxes for ages. The SQL:1999 standard had introduced almost equivalent "lateral derived tables", which are finally supported with PostgreSQL 9.3, or Oracle 12c, which has adopted both the SQL standard LATERAL syntax and the T-SQL vendor-specific CROSS APPLY and OUTER APPLY syntaxes. … Continue reading Add LATERAL Joins or CROSS APPLY to Your SQL Tool Chain
Oracle 12c Goodies: { CROSS | OUTER } APPLY
I can't believe my eyes. Has this been openly communicated by Oracle? I haven't seen too many blog posts on that matter. Apart from introducing the awesome SQL Standard OFFSET .. FETCH clause (just like SQL Server 2012), there seems to be now also { CROSS | OUTER } APPLY in Oracle 12c! Check out the … Continue reading Oracle 12c Goodies: { CROSS | OUTER } APPLY
jOOQ as a “PL/Java” language
Some people who get in touch with PL/SQL, PL/pgSQL, T-SQL, or any other proprietary procedural language for SQL interaction are probably missing out on a couple of language integration features in the Java world. Most Java APIs see SQL as an external domain-specific language that is "best" dealt with using string concatenation. Such APIs include: … Continue reading jOOQ as a “PL/Java” language