LibreOffice Base SQL: Query Parameters

Today, I will continue the discussion of SQL queries with queries that involve parameters in queries. The SQL in this post is based on the TeamMembers database. For instructions on setting up the database in LibreOffice and a short description of the database and tables, see the post at http://wp.me/pvwht-a4.

Parameters in Queries

Imagine that you need to query the database several times a day to know who is on what project in order to arrange meetings and disseminate information about the project. You could write a new query for each project, but this could become unmanageable over time. The SQL would look something like

SELECT “FirstName”, “LastName”
FROM “TeamMembers”
JOIN “TeamMember2Project”
ON “TeamMembers”.”ID” = “TeamMember2Project”.”TeamMemberID”
JOIN “Projects”
ON “TeamMember2Project”.”ProjectID” = “Projects”.”ID”
WHERE “Projects”.”ID” = 3;

where the ID queried in the where clause would change for each project. Repeat that a few hundred times, and you start to clog up you database with queries.

Lucky for us, Base gives us a way to reuse queries using parameters in our queries. A parameter is a placeholder for the value you will supply when you run the query. A parameter is a colon followed by a placeholder name, i.e. :myParameter.

Here is the same query written with a parameter in place of the value for the project ID in the WHERE clause.

SELECT “FirstName”, “LastName”
FROM “TeamMembers”
JOIN “TeamMember2Project”
ON “TeamMembers”.”ID” = “TeamMember2Project”.”TeamMemberID”
JOIN “Projects”
ON “TeamMember2Project”.”ProjectID” = “Projects”.”ID”
WHERE “Projects”.”ID” = :ProjectID;

When you run this query, you are prompted by a dialog box asking for a value for ProjectID. Enter the ID for one of the projects and get your results.

Using parameters in your queries allows you to reuse common queries by replacing values with prompted parameters.

Written with StackEdit.