procedure sql code example

Example 1: procedure in sql

-Stored procedure is a group of SQL
statements that has been created 
and stored in the database.
-A stored procedure will accept input 
parameters so that a single procedure
can be used over the network by several
clients using different input data.
-A stored procedures will reduce
network traffic and increase the performance. 
If we modify a stored procedure all the
clients will get the updated stored procedure.

Sample of creating a stored procedure
CREATE PROCEDURE test_display AS
SELECT FirstName, LastName FROM tb_test
EXEC test_display

Example 2: create proc

IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL   
    DROP PROCEDURE Production.uspGetList;  
GO  
CREATE PROCEDURE Production.uspGetList @Product varchar(40)   
    , @MaxPrice money   
    , @ComparePrice money OUTPUT  
    , @ListPrice money OUT  
AS  
    SET NOCOUNT ON;  
    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'  
    FROM Production.Product AS p  
    JOIN Production.ProductSubcategory AS s   
      ON p.ProductSubcategoryID = s.ProductSubcategoryID  
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;  
-- Populate the output variable @ListPprice.  
SET @ListPrice = (SELECT MAX(p.ListPrice)  
        FROM Production.Product AS p  
        JOIN  Production.ProductSubcategory AS s   
          ON p.ProductSubcategoryID = s.ProductSubcategoryID  
        WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);  
-- Populate the output variable @compareprice.  
SET @ComparePrice = @MaxPrice;  
GO

Example 3: sql procedure

CREATE OR REPLACE PROCEDURE my_scheme.my_procedure(param1 IN VARCHAR2) IS
    cnumber NUMBER;
BEGIN
    cnumber := 10;
    INSERT INTO my_table (num_field) VALUES (param1 + cnumber);
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        raise_application_error(-20001, 'An error was encountered - '
            || sqlcode || ' -ERROR- ' || sqlerrm);
END;