Sql stored procedure

This article is tagged with: Sql stored procedure and mysql

sql create stored procedure

----------------------------------------------------------------------------------------------------
-- MySQL (scroll down for Oracle)
-- example
CREATE OR REPLACE PROCEDURE select_employees()
BEGIN
select *
from employees
limit 1000;
END; -- Reset the delimiter
/* syntax:
CREATE PROCEDURE <your-procedure-name>(<argument1><argument2>...<argumentn>)
BEGIN
<code-that-stored-procedure-executes>;
END;
*/
----------------------------------------------------------------------------------------------------
-- Oracle
-- EXAMPLE
CREATE OR REPLACE PROCEDURE select_employees()
BEGIN
select *
from employees
limit 1000;
END;
/* SYNTAX
CREATE OR REPLACE PROCEDURE <your-procedure-name>(<argument1><argument2>...<argumentn>)
BEGIN
<code-that-stored-procedure-executes>; -- Remember, the delimiters within the stored procedure remain ;
END;
*/
----------------------------------------------------------------------------------------------------</code-that-stored-procedure-executes></argumentn></argument2></argument1></your-procedure-name></code-that-stored-procedure-executes></argumentn></argument2></argument1></your-procedure-name>

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

sql stored procedure

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

This article is tagged with: Sql stored procedure and mysql