Procedure — SQL Technical Interview Questions & Answers

Procedure

Procedure

  • A stored procedure is a named batch of SQL on the server. CREATE PROCEDURE fail_list() BEGIN SELECT name, marks FROM students WHERE marks < 40; END; CALL fail_list();
  • Use for a repeated admin task. Don’t put all app logic in procedures — versioning is harder than Git for Java/PHP. DELIMITER $$ is needed in the CLI because ; is inside the body.
  • Trap: procedure vs function: FUNCTION returns a value and can be in a SELECT. PROCEDURE is CALL’d and can return result sets.
  • On the example next to this theory: Procedure: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Technical Interview Tip: CREATE PROCEDURE … BEGIN … END; CALL name();

Code Examples for Procedure

Procedure — sample query

-- Procedure
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Procedure: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

How to Explain Procedure to an Interviewer

How do you run a procedure?

CALL proc_name();