CREATE PROCEDURE syntax in SQL - Step-by-Step Execution

Concept Flow - CREATE PROCEDURE syntax

Start CREATE PROCEDURE

↓

Define Procedure Name

↓

Specify Parameters (optional)

↓

Write Procedure Body

↓

End with DELIMITER or END

↓

Procedure Stored in Database

This flow shows how a stored procedure is created step-by-step: naming it, adding parameters, writing the body, and saving it.

Execution Sample

SQL

DELIMITER $$
CREATE PROCEDURE greetUser(IN userName VARCHAR(50))
BEGIN
  SELECT CONCAT('Hello, ', userName) AS greeting;
END$$
DELIMITER ;

This code creates a procedure named greetUser that takes a name and returns a greeting message.

Execution Table

Step	Action	Evaluation	Result
1	Set DELIMITER to $$	DELIMITER changed	Allows procedure body with semicolons
2	CREATE PROCEDURE greetUser	Procedure name set	Procedure greetUser defined
3	Define parameter IN userName VARCHAR(50)	Parameter accepted	Input parameter userName ready
4	BEGIN block starts	Procedure body begins	Ready to write SQL statements
5	SELECT CONCAT('Hello, ', userName) AS greeting;	Concatenate string	Returns greeting message
6	END block ends	Procedure body ends	Procedure body complete
7	DELIMITER reset to ;	DELIMITER restored	Procedure stored successfully

💡 Procedure creation ends after DELIMITER reset; procedure is stored in database.

Variable Tracker

Variable	Start	After Step 3	After Step 5	Final
userName	undefined	Input parameter defined	Used in CONCAT	Input parameter used in procedure

Key Moments - 3 Insights

Why do we change the DELIMITER before and after creating the procedure?

We change DELIMITER (see execution_table step 1 and 7) to allow semicolons inside the procedure body without ending the CREATE statement early.

What does the IN keyword before userName mean?

IN means userName is an input parameter to the procedure (execution_table step 3), so the caller must provide a value.

Why do we use BEGIN and END inside the procedure?

BEGIN and END (steps 4 and 6) group multiple SQL statements as the procedure body, even if there is only one statement.

Visual Quiz - 3 Questions

Look at the execution_table, what happens at step 5?

A The greeting message is created using CONCAT

B The procedure name is set

C The DELIMITER is reset

D The parameter userName is defined

💡 Hint

Check the Action and Evaluation columns at step 5 in the execution_table

At which step does the procedure body start?

A Step 2

B Step 4

C Step 3

D Step 6

💡 Hint

Look for 'BEGIN block starts' in the Action column of execution_table

If we did not change the DELIMITER at step 1, what would happen?

A The parameter would not be recognized

B The procedure would be created successfully

C The CREATE PROCEDURE statement would end too early at the first semicolon

D The procedure body would be ignored

💡 Hint

Refer to the key_moments explanation about DELIMITER usage

Concept Snapshot

CREATE PROCEDURE syntax:
DELIMITER $$
CREATE PROCEDURE procName([IN|OUT|INOUT] param datatype)
BEGIN
  -- SQL statements;
END$$
DELIMITER ;
Use DELIMITER to allow semicolons inside procedure body.
Parameters can be input (IN), output (OUT), or both (INOUT).

Full Transcript

Creating a stored procedure in SQL involves several steps. First, we change the DELIMITER to allow semicolons inside the procedure body without ending the statement early. Then, we write CREATE PROCEDURE followed by the procedure name and optional parameters. The procedure body is enclosed between BEGIN and END, where SQL statements are written. Finally, we reset the DELIMITER to the default. This process stores the procedure in the database for later use.