47  SQL Stored Procedures

There are no stored procedures in sqlite but both oracle and sql server have them. Normally, stored procedures and functions are written using the sql dialects. For Oracle PL/SQL and for Sql Server T-SQL.

But these two databases and sqlite also allows using other languages for writing functions or stored procedures.

• Sqlite allows writing C language functions.
• SQL Server allows .NET languages (for example: C#) for stored procedures
• Oracle allows java, .NET, javascript, and programming languages for stored procedures.

A stored procedure is a group of SQL statements in data dictionary. These SQL statements are mostly dialects of given database, PL-SQL and T-SQL. You could call these procedures using their name.

I personally do not like to use stored procedures much since they are hard to control using standard version control (git) systems. Most of the time, writing necessary code in given backend code is a better choice. But database vendors provide a lot of ready to use stored procedures. Instead of writing custom code, using these ready stored procedures or functions is a always better.

47.1 Oracle Stored Procedure Example

CREATE OR REPLACE PROCEDURE Create_Employee 
(firstname IN varchar2,
lastname IN varchar2 , 
id OUT number)
IS
BEGIN
  INSERT INTO CHINOOK."Employee" 
  ( "LastName", "FirstName") 
  VALUES(firstname,lastname) 
  returning "EmployeeId" into id;   
END;

1. Open Procedures folder in oracle dbeaver chinook connection
2. refresh this folder to see the create stored procedure
3. Use mouse right click and select execute procedure, see image below.

dbeaver-oracle-execute-procedure

4. In the opened window, you will see following sql.

CALL CHINOOK.CREATE_EMPLOYEE(:FIRSTNAME, :LASTNAME, ?);

5. Change the variables, with your first name and last name like below.

CALL CHINOOK.CREATE_EMPLOYEE('Atilla', 'Özgür', ?);

6. execute the script. In the results window, you should see the newly inserted employee id.

47.2 SQL Server Stored Procedure Example

1. Open the chinook sql server connection

2. copy paste the following script to a new script window

CREATE PROCEDURE Create_Employee
(
    @FirstName nvarchar(50) = NULL,
    @LastName nvarchar(50) = NULL
)
AS
BEGIN
DECLARE @ID int

INSERT INTO Employee
( LastName, FirstName) 
VALUES(@FirstName,@LastName) 

  SELECT @ID = scope_identity();
  RETURN @ID
END

3. Run the script to create the stored procedure

4. Unfortunately, current version of dbeaver does not support sql server stored procedure in GUI.

5. Open a new script window and copy paste the following script

DECLARE @RC int
EXECUTE @RC = Create_Employee 'Atilla','Özgür'
SELECT @RC

5. execute the script.
6. If we check it in table, we see that new employee inserted but dbeaver community edition does not show id information.

If we execute same query in microsoft tools, we see the new inserted id. For example vscode sql server extension shows the id value. See below image:

In the results window, you should see the newly inserted employee id.

sqlserver-stored-procedure-execution-vscode