dots Created with Sketch.
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 (;).

amazon

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Jul 22 Is there a CSS parent selector? #Jul 22 Difference between :where and :is in CSS #Jul 22 Does :is() pseudo selector hint at CSS preprocessing in the future? #Jul 22 Control Scrolling with CSS Scroll Snap #Jul 21 Control rendering using CSS content-visibility property #Jul 21 How to use @supports rule in CSS?
You might also like these
How to change style of nth-child using jQuery?jQueryHow does while loop work in SCSS?SCSSHow to Sort (Shuffle) an Array in Random Order in JavaScript?JavaScriptHow to get ACF values from custom post type?WordPressThe fundamental difference between HAVING and WHERE clauses in PostgresPostgresA simple example on grid ‘auto-fill’ vs ‘auto-fit’CSS