• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

I Like Kill Nerds

The blog of Australian Front End / Aurelia Javascript Developer & brewing aficionado Dwayne Charrington // Aurelia.io Core Team member.

  • Home
  • Aurelia 2
  • Aurelia 1
  • About
  • Aurelia 2 Consulting/Freelance Work

What’s The Difference Between Views and Stored Procedures in SQL?

General · August 1, 2014

There is a lot of confusion when it comes to MySQL and views versus stored procedures. Many people often wonder what the difference is between the two of them?

Views

It is all in the name. Views are essentially virtual tables comprised of pre-written SQL statements. You might have a view for displaying users who have joined in the last couple of days. Think of views as a means of getting data from your database without having to write or call queries constantly.

A view cannot accept parameters and also is limited to just one SELECT query as well as not being able to perform modifications to any table. They’re very much used for simplifying data queries.

You can query a view like you would any table, it is a virtual table after all and thus can be used in a query alongside other views to form a query. To select data from a view called “popularUsers” you can simply go: SELECT * FROM popularUsers

Stored Procedures

One of the definitions of the word “procedure” is: a series of actions conducted in a certain order or manner. A stored procedure is a set of one or more queries that allow for dynamic parameters contained within something resembling that of a function, only called a stored procedure.

A stored procedure can contain loops, if/else statements and other logic you might usually see within a function if you’ve worked with any programming language before. It means you can create functions for getting popular users for example and allow date ranges to be specified to the procedure which will affect the results.

From a performance perspective, a stored procedure is often easier to tune to be more effective in comparison to a view and you should always use a stored procedure where you can. They also neatly encapsulate logic keeping things cleaner, they can be used to abstract your schema for security reasons and they’re easier to test.

Both have their purposes and can achieve relatively the same result. A stored procedure offers you greater modularity, can easily be optimised and customised and allow for cool things like loops, if/else statements and other things views cannot. If performance is important to you and your working with a lot of tables and rows, a stored procedure might just stop you from tearing out your hair and smashing your head against your desk violently.

Dwayne

Leave a Reply Cancel reply

0 Comments
Inline Feedbacks
View all comments

Primary Sidebar

Popular

  • Thoughts on the Flipper Zero
  • Testing Event Listeners In Jest (Without Using A Library)
  • How To Get The Hash of A File In Node.js
  • How To Paginate An Array In Javascript
  • Waiting for an Element to Exist With JavaScript
  • Reliably waiting for network responses in Playwright
  • How To Get Last 4 Digits of A Credit Card Number in Javascript
  • Neural DSP Reveal Details About the Long-Awaited Quad Cortex Desktop Editor
  • How to Use Neural DSP Archetype Plugins With the Quad Cortex
  • How To Install Eufy Security Cameras Without Drilling or Using Screws

Recent Comments

  • Kevmeister68 on Start-Ups and Companies That Embrace Work From Anywhere Will Be More Likely to Survive the Coming Recession in 2023
  • kevmeister68 on What Would Get People Back Into the Office?
  • Dwayne on PHP Will Not Die
  • Dwayne on How to Create a Blockchain With TypeScript
  • kevmeister68 on PHP Will Not Die

Copyright © 2023 · Dwayne Charrington · Log in

wpDiscuz