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

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

Posted by: Guest on March-17-2020

Source

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;

Code answers related to "procedure in sql"

Browse Popular Code Answers by Language

Answers for "procedure in sql"

Code answers related to "procedure in sql"

Code answers related to "SQL"

Browse Popular Code Answers by Language

Popular Programming Languages

Advertisements

Company

Compilers

Help

Connect with us