A data type identifies the kind of value a column, variable, or parameter holds. Common examples are character strings, integers, decimals, floats, dates, and times. The type matters because numbers, names, and dates follow different filtering rules, and compatible types are required when combining query results. section 13.1.1 section 5.1 section 10.1
When designing a table, choose a data type for every column alongside its name. For example, a CategoryID primary-key column can use int, allowing a unique numeric value for each category row. Clear column names and appropriate types make a database easier to query and understand. section 18.1.1
Matching a procedure parameter
A parameter that evaluates an OrderID should use int when OrderID is an integer column. Matching parameter, variable, and column types avoids extra work by the RDBMS and helps prevent errors; in MySQL, SHOW COLUMNS can reveal a column's type. section 20.5 section 20.2.3 section 21.2.1
MySQL user-defined variables infer their type from the assigned value: a quoted title becomes a string, while a numeric value becomes numeric. A variable beginning as NULL has no meaningful type until another value is assigned. Although MySQL permits a variable's type to change later, reusing one variable for different types is best avoided. section 13.1.1 section 13.4.2 section 13.4.3
DELIMITER //
CREATE PROCEDURE AddPromotion (
IN _PromotionID int,
IN _PromotionCode varchar(10),
IN _PromotionStartDate datetime,
IN _PromotionEndDate datetime
)
BEGIN
IF _PromotionCode IS NOT NULL THEN
INSERT INTO promotion (
PromotionID,
PromotionCode,
PromotionStartDate,
PromotionEndDate
)
SELECT
_PromotionID,
_PromotionCode,
_PromotionStartDate,
_PromotionEndDate
;
END IF;
END //
DELIMITER ;
This procedure declares int, varchar(10), and datetime parameters to match the values written to the promotion table. section 21.2.1
Type declarations vary by database product. MySQL can infer the type of a user-defined variable assigned with SET, but variables declared inside stored procedures use DECLARE with an explicit type. SQL Server and PostgreSQL also require declared user-defined variables to specify a type. section 13.1.1 section 22.1.1
