What are Stored Procedures for SQL Server?

In simplest terms, a stored procedure is a bunch of SQL code that’s stored somewhere and you can use it anytime you want. Think of it as a SCSS function or any programming language function that you can call and it will execute some code and will return something.

A stored procedure is a prepared SQL queries that can be reused. It’s like components in React. So, rather than writing an SQL query multiple times, you can just create a stored procedure and call it every time you need to run that query.

Just like functions, you can pass parameters and make it return dynamic results.

Stored Procedures Syntaxes

Stored Procedures Syntax in SQL

CREATE PROCEDURE name
AS
query
GO;

GO statement was intended for Management studio and other tools.

Stored Procedures Syntax in MySQL

Here’s another syntax with BEGIN … END that is used in MySQL.

CREATE PROCEDURE name
AS
BEGIN
  query
END;

Stored Procedures Execution Syntax in SQL

Here’s the execution command syntax:

EXECUTE procedure_name;

OR

EXEC procedure_name;

Stored Procedures Execution Syntax in MySQL

CALL procedure_name;

Stored Procedures Examples in SQL

Stored Procedures Example in SQL with parameter

CREATE PROCEDURE country_hos @con char(20)
AS
  SELECT Name, HeadOfState FROM Country
  WHERE Continent = @con;
GO;

This is how you call the procedure above:

EXEC country_hos @con = 'Europe';

Stored Procedures Example in SQL without parameter

CREATE PROCEDURE country_hos
AS
  SELECT Name, HeadOfState FROM Country
GO;

This is how you call the procedure above:

EXEC country_hos;

Stored Procedures Example in MySQL

Stored Procedures Example in MySQL with parameter

Here’s a one example from MySQL official docs.

DELIMITER //
CREATE PROCEDURE country_hos
(IN con CHAR(20))
BEGIN
  SELECT Name, HeadOfState FROM Country
  WHERE Continent = con;
END //
DELIMITER ;

This is how you call the procedure above:

CALL country_hos('Europe');

Stored Procedures Example in MySQL without parameter

You can just remove the parameters and not pass anything at all.

DELIMITER //
CREATE PROCEDURE country_hos
BEGIN
  SELECT Name, HeadOfState FROM Country
END //
DELIMITER ;

This is how you call the procedure above:

CALL country_hos;

What’s a DELIMITER?

DELIMITER keyword in the procedure above is a function of the mysql client command line.

Delimiters are mostly used when defining stored procedures, functions or triggers where you need to define multiple statements.

The examples above uses the delimiter to change the statement delimiter from ; to //

Inside the procedure, the statements are terminated by semicolon (;).

Share this post