SQL SPECIFIC

What is the SQL SPECIFIC keyword?

Assigns a unique, unambiguous name to a stored routine so that a particular overloaded function or procedure can be referenced or dropped.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SPECIFIC:

SQL SPECIFIC Full Explanation

SPECIFIC is part of the SQL standard’s routine-handling syntax. When you create a FUNCTION or PROCEDURE you may append a SPECIFIC clause that declares a secondary identifier (the specific name) for that routine. Unlike the routine’s invocation name, the specific name must be unique within the schema and does not participate in overloading resolution. It exists solely so you can later reference that exact routine – even if multiple routines share the same invocation name but differ in parameter lists.Key points:- Supported mainly by standards-compliant systems such as IBM Db2 and some ANSI-mode engines.- Aids in DROP and ALTER statements that otherwise need the full parameter signature to disambiguate an overloaded routine.- Has no impact on how a routine is called from SQL or from client code.- If not supplied, the database engine automatically generates a specific name.- Attempting to reuse a specific name in the same schema raises an error.

SQL SPECIFIC Syntax

-- Creating a routine with a specific name
CREATE FUNCTION schema.func_name (p1 INT, p2 INT)
RETURNS INT
SPECIFIC schema.func_total
DETERMINISTIC
RETURN p1 + p2;

-- Dropping by specific name
DROP SPECIFIC FUNCTION schema.func_total;

SQL SPECIFIC Parameters

specific_name (identifier) - The unique label assigned after the SPECIFIC keyword

Example Queries Using SQL SPECIFIC

-- 1. Two overloaded functions
CREATE FUNCTION sales.tax(rate DECIMAL(5,2)) RETURNS DECIMAL(5,2)
SPECIFIC sales.tax_rate
RETURN rate * 0.01;

CREATE FUNCTION sales.tax(subtotal DECIMAL(9,2), rate DECIMAL(5,2))
RETURNS DECIMAL(9,2)
SPECIFIC sales.tax_amount
RETURN subtotal * rate * 0.01;

-- 2. Drop only the single-parameter version
DROP SPECIFIC FUNCTION sales.tax_rate;

Expected Output Using SQL SPECIFIC

Each CREATE FUNCTION registers a new routine and returns "Function created" (or similar)
The DROP statement removes only the routine whose specific name matches sales
tax_rate, leaving the two-parameter version intact

Use Cases with SQL SPECIFIC

Managing overloaded routines where multiple versions share the same invocation name
Simplifying ALTER or DROP operations without spelling out full parameter signatures
Enforcing clearer governance in large schemas that contain hundreds of similar routines

Common Mistakes with SQL SPECIFIC

Assuming SPECIFIC changes how you call the routine (it does not)
Forgetting that specific names must be unique inside a schema
Using SPECIFIC in dialects that do not support it (PostgreSQL, MySQL, SQL Server, Oracle)

First Introduced In

SQL:1999

Frequently Asked Questions

What problem does SPECIFIC solve?

It removes ambiguity when multiple overloaded routines share the same name by giving each one a unique label you can later use in DROP or ALTER statements.

Does SPECIFIC affect how I call the routine?

No. Clients still invoke the routine by its normal name. SPECIFIC is strictly a metadata label.

Can I change a specific name after creation?

Not directly. You must drop the routine and recreate it with a new SPECIFIC clause or use ALTER FUNCTION ... SPECIFIC where supported.

What happens if I duplicate a specific name?

The database raises an error indicating the specific name already exists in the schema. Choose a different identifier.