Mysql Declare Variables

Mysql Declare Variables

1. User-defined variables
You can access any user-defined variable without declaring it or initializing it.

You can initialize a variable using SET or SELECT statement:

SET @start = 1, @finish = 100;

Or

SET @start := 1, @finish := 100;

2. Local Variables 
No Prefix

Local variables needs to be declared using DECLARE before accessing it.

Example: 

DELIMITER $$

CREATE DEFINER = 'user'@'localhost'
PROCEDURE ADDINDEXMYSQL_sp (IN schemaName varchar(500), IN tableName varchar(500), IN indexName varchar(500), IN alterStmt varchar(2000))
BEGIN
  DECLARE count int DEFAULT 0;

  SELECT
    COUNT(*) INTO count
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = schemaName
  AND TABLE_NAME = tableName
  AND INDEX_NAME = indexName;
  IF count = 0
  THEN
    SET @query = CONCAT('ALTER TABLE ', schemaName, '.', tableName, ' ADD INDEX ', indexName, ' ', alterStmt);
    PREPARE qry FROM @query;
    EXECUTE qry;

    DEALLOCATE PREPARE qry;
  END IF;
END
$$

3 . Server System Variables 

Exmaple

To see the current values used by a running server

Select @@Version;