-- without parameters; create
CREATE [OR ALTER] PROCEDURE <pname>
AS
BEGIN
<PROCEDURE BODY/ STATEMENTS>
END
-- call procedure
EXECUTE / EXEC <pname>;
(OR)
EXECUTE / EXEC <pname> VALUE / (S)

CREATE TABLE Products
( Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL
);
--1
GO
CREATE PROCEDURE AddProduct @name NVARCHAR(20), @manufacturer NVARCHAR(20), @count INT, @price MONEY
AS
INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price)
VALUES(@name, @manufacturer, @count, @price)
DECLARE @prodName NVARCHAR(20), @company NVARCHAR(20);
DECLARE @prodCount INT, @price MONEY
SET @prodName = 'Galaxy C7'
SET @company = 'Samsung'
SET @price = 22000
SET @prodCount = 5
EXEC AddProduct @prodName, @company, @prodCount, @price
SELECT * FROM Products
--2
GO
CREATE PROCEDURE AddProductWithOptionalCount @name NVARCHAR(20), @manufacturer NVARCHAR(20), @price MONEY, @count INT = 1
AS
INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price)
VALUES(@name, @manufacturer, @count, @price)
DECLARE @prodName NVARCHAR(20), @company NVARCHAR(20), @price MONEY
SET @prodName = 'Redmi Note 5A'
SET @company = 'Xiaomi'
SET @price = 22000
EXEC AddProductWithOptionalCount @prodName, @company, @price
SELECT * FROM Products
--3
GO
CREATE PROCEDURE GetPriceStats @minPrice MONEY OUTPUT, @maxPrice MONEY OUTPUT
AS
SELECT @minPrice = MIN(Price), @maxPrice = MAX(Price)
FROM Products
DECLARE @minPrice MONEY, @maxPrice MONEY
EXEC GetPriceStats @minPrice OUTPUT, @maxPrice OUTPUT
PRINT 'Минимальная цена ' + CONVERT(VARCHAR, @minPrice)
PRINT 'Максимальная цена ' + CONVERT(VARCHAR, @maxPrice)
--4
GO
CREATE PROCEDURE CreateProduct @name NVARCHAR(20), @manufacturer NVARCHAR(20), @count INT, @price MONEY, @id INT OUTPUT
AS INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price) VALUES(@name, @manufacturer, @count, @price) SET @id = @@IDENTITY
DECLARE @id INT
EXEC CreateProduct 'LG V30', 'LG', 3, 28000, @id OUTPUT
PRINT @id
--5
GO
CREATE PROCEDURE GetAvgPrice AS
DECLARE @avgPrice MONEY
SELECT @avgPrice = AVG(Price)
FROM Products
RETURN @avgPrice;
DECLARE @result MONEY
EXEC @result = GetAvgPrice
PRINT @result

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

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;

 create procedure procedure name as select*from dbo.tablewherecondition exec

{"tags":[{"tag":"textarea","content":"Alter PROCEDURE [dbo].[insert_data] \n\t@Employee_name nchar(30), \n\t@Department nchar(30),\n @Email AS varchar(26) = NULL\nAS\nBegin\n SET NOCOUNT ON;\n\t INSERT INTO employee\n\t\t\t (EmployeeName, Department, EmailId)\n\t VALUES (@Employee_name ,@Department ,@Email )\n\t SELECT top 1 EmployeeID,EmployeeName, Department, EmailId\n\t\tFROM employee\n\t\torder by EmployeeID desc\nEnd\nGO\n\nexec insert_data 'janta','QA','[email protected]'","code_language":"whatever"}]}