SQL

stored procedure sql

-- 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)

procedure syntax

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

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;

stored procedure sql

-- 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)

stored procedure

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

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace AutomationApp
{
    class Program
    {
        public void RunStoredProc()
        {
            SqlConnection conn = null;
            SqlDataReader rdr  = null;

            Console.WriteLine("
Top 10 Most Expensive Products:
");

            try
            {
                conn = new SqlConnection("Server=(local);DataBase=master;Integrated Security=SSPI");
                conn.Open();
                SqlCommand cmd = new SqlCommand("dbo.test", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                rdr = cmd.ExecuteReader();
                /*while (rdr.Read())
                {
                    Console.WriteLine(
                        "Product: {0,-25} Price: ${1,6:####.00}",
                        rdr["TenMostExpensiveProducts"],
                        rdr["UnitPrice"]);
                }*/
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                if (rdr != null)
                {
                    rdr.Close();
                }
            }
        }
        static void Main(string[] args)
        {
            Console.WriteLine("Hello World");
            Program p= new Program();
            p.RunStoredProc();      
            Console.Read();
        }
    }
}