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 How to check if radio button is checked or not using JavaScript? #2 How to add Read More Read Less Button using JavaScript? #3 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #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

#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 #Jan 14 How to display a student’s individual transcript in Colleague? #Jan 11 How to install PuTTY on a MacOS?
You might also like these
The Sort and Compare Functions in JavaScriptJavaScriptAmpersand (Parent Selector) in SCSS (Sass)SCSSGap in Flex?CSSHow to vertically align a html radio button to it’s label?CSSCurrencyPipe in Angular 9 with ExamplesAngularIntroduction to Angular modules Part 1: NgModule metadataAngular