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

Stored Procedure :
A stored procedure is a prepared SQL code that you can save,
so the code can be reused over and over again.
So if you have an SQL query that you write over and over again,
save it as a stored procedure, and then just call it to execute it.
You can also pass parameters to a stored procedure,
so that the stored procedure can act based on the parameter value(s)
that is passed.

 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"}]}