Skip to main content

Stored Procedure

Introduction

A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure (sometimes called a proc, sproc, StoPro, StoredProc, sp or SP) is actually stored in the database data dictionary.
Typical use for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures can consolidate and centralize logic that was originally implemented in applications. Extensive or complex processing that requires execution of several SQL statements is moved into stored procedures, and all applications call the procedures. One can use nested stored procedures by executing one stored procedure from within another.
Stored procedures are similar to user-defined functions (UDFs). The major difference is that UDFs can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement.

SQL Dialects Reference/Procedural language/Stored procedures

 

Database
Create syntax
Calling
DB2


CREATE PROCEDURE procedure_name(...) BEGIN /* SQL code */ END


CALL procedure_name(...)
SQLite
N/A
 N/A
MySQL


DELIMITER $$CREATE PROCEDURE nameprocedure (input_parameter_name datatype, ... )BEGIN /* SQL code */END$$DELIMITER ;


CALL nameprocedure(...)
PostgreSQL


CREATE FUNCTION function_name (input_parameter_name datatype, ...)RETURNS return_typeAS $$DECLARE variable_name datatype;BEGIN /* SQL code */END;$$ LANGUAGE plpgsql;


SELECT function_name(...)
Firebird


SET TERM $$ ;CREATE PROCEDURE nameprocedure (input_parameter_name datatype, ... ) RETURNS (output_parameter_name datatype, ... )AS DECLARE VARIABLE variable_name datatype;BEGIN /* SQL code */END$$SET TERM ; $$


SELECT ... FROM function_name(...)


EXECUTE function_name(...)






SQL Server


CREATE PROCEDURE nameprocedure (input_parameter_name datatype, ... )AS /* SQL code */GO


EXEC nameprocedure(...)
Linter


CREATE [OR REPLACE] PROCEDURE procedure_name([IN/OUT/INOUT] parameter_name datatype, ...) [RESULT datatype] [FOR DEBUG] DECLARE /* variables declaration */ CODE /* stored procedure code (including SQL code)*/ EXCEPTIONS /* exceptions declarations */ END


CALL procedure_name(...)EXECUTE procedure_name(...)EXECUTE procedure_name(...) AS OWNERSELECT procedure_name(...)FROM ...WHEREprocedure_name(...) = ...

 



If you like this post, please subscribe us
Enter your email address:


Labels:

Comments

Post a Comment

Important - Make sure to click the Notify Me check-box below the comment to be notified of follow up comments and replies.

Popular posts from this blog

Convert your datatable into generic poco object in c# using linq, ado and reflections.

Follow @harshit_parshii The most common problem that we face these days is to create a common class and method that can be used across all the projects and codes. So today I will be sharing my code where you can see how to make and create a generic function without using entity framework for ado. net. The scenario is like you have an old software that uses stored procedure to return set of entities as a data-table, you do not want to re-write the back-end code as you are creating a web API in c# which needs to be delivered asap. You need to map these data tables to models as you might be using MV* pattern. So here we will be doing one to one mapping of model to data- table, and in similar fashion insert or update can also be done. So basically we are converting a data-table to list of strongly typed object model to do CRUD operations. So we have following things before hand. A helper class is referenced as the database(dbFactory) which executes ado. ne...
Post a Comment
Read more

Send a Fax in windows using faxcomexlib and TAPI in VB code .Net

An application that provides sending fax from faxmodem, connected to the computer, will be explained in the following post.  We can use Telephony Application Programming Interface (TAPI) and the Fax Service Extended Component Object Model (COM) API to send fax. The fax service is a Telephony Application Programming Interface (TAPI)-compliant system service that allows users on a network to send and receive faxes from their desktop applications. The service is available on computers that are running Windows 2000 and later. The fax service provides the following features: Transmitting faxes Receiving faxes Flexible routing of inbound faxes Outbound routing Outgoing fax priorities Archiving sent and received faxes Server and device configuration management Client use of server devices for sending and receiving faxes Event logging Activity logging Delivery receipts Security permissions The following Microsoft Visual Basic code example sends a fax. Note that...
Post a Comment
Read more

DTMF (Mobile) based speed control of AC motor.

Introduction: With the advancement of ages from prehistoric to present day scenario our life has become more sophisticated and busy, so to ease the schedule of this busy life, technology has play the vital role in it and for its proper running of machine the technology has gone further by providing digitization of analog machinery and its use is enhanced day by day. This project is based on the same concept by wireless controlling the machinery through mobile system anywhere from the world. This project aims at Speed Control of AC motor using DTMF method; DTMF stands for dual tone multiple frequencies . The main idea of this project is to control the speed of an AC motor by wireless communication using DTMF decoder technique aiming at the fine use of mobile technology in our day to day use of automated products. Mobile phones have different frequencies for each number printed on it. These numbers when pressed during call duration produces a tone of certain frequency. This frequ...
9 comments
Read more