CREATE PROCEDURE syntax in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of the CREATE PROCEDURE statement in SQL?

It is used to create a stored procedure, which is a saved set of SQL statements that can be executed repeatedly.

beginner

Which keyword starts the definition of a stored procedure in SQL?

The keyword CREATE PROCEDURE starts the definition.

intermediate

How do you specify input parameters in a stored procedure?

Input parameters are listed in parentheses after the procedure name, each with a name and data type, e.g., (IN param_name INT).

beginner

What keyword is used to mark the start of the procedure body?

The keyword BEGIN marks the start of the procedure body, and END marks its end.

intermediate

Why use stored procedures instead of running SQL statements directly?

Stored procedures help reuse code, improve performance, and enhance security by controlling access and reducing SQL injection risks.

Which of the following is the correct way to start creating a stored procedure named GetUser?

A CREATE PROCEDURE GetUser()

B MAKE PROCEDURE GetUser()

C NEW PROCEDURE GetUser()

D START PROCEDURE GetUser()

The correct syntax to create a stored procedure starts with CREATE PROCEDURE followed by the procedure name.

How do you define an input parameter named userId of type INTEGER in a procedure?

A (userId INTEGER)

B (PARAM userId INTEGER)

C (INPUT userId INTEGER)

D (IN userId INTEGER)

Input parameters are defined with the IN keyword followed by the parameter name and data type.

What keywords enclose the body of a stored procedure?

A BEGIN ... END

B BEGIN ... FINISH

C OPEN ... CLOSE

D START ... STOP

The procedure body is enclosed between BEGIN and END keywords.

Which of these is NOT a benefit of using stored procedures?

A Improved security

B Slower query execution

C Code reuse

D Easier maintenance

Stored procedures usually improve performance, so slower execution is not a benefit.

In SQL, what does the CREATE PROCEDURE statement do?

A Deletes a procedure

B Executes a procedure

C Creates a new stored procedure

D Updates an existing procedure

CREATE PROCEDURE is used to create a new stored procedure.

Explain the basic syntax structure of the CREATE PROCEDURE statement.

Think about how you name the procedure, add parameters, and write the commands it runs.

Describe why stored procedures are useful in managing database operations.

Consider how stored procedures help both developers and database systems.