Yogesh Chauhan's Blog

What are Stored Procedures for SQL Server?

in SQL/MySQL on June 16, 2021

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 (;).


Most Read

#1 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #2 How to add Read More Read Less Button using JavaScript? #3 How to check if radio button is checked or not using JavaScript? #4 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #5 How to uninstall Cocoapods from the Mac OS? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface
You might also like these
How to create a flip pricing table using CSS and JavaScript?CSSHow to make WordPress main stylesheet (style.css)?WordPressOpen Source Security Tools for Defense SecurityMiscellaneousHow destructuring works in React?ReactHAVING Clause in Postgres with Examples in All Aggregate FunctionsPostgresSolution for the error Commit failed – exit code 1 received in Desktop GithubMiscellaneous