--- NOTE:  Code Commenting In Stored Procedures

---
--- Description              : [ CONTENT DESCRIPTION ]
--- ------------------------------------------------------------
--- Developer                : DEVELOPER NAME
--- Date Created             : Mon  D, YEAR
--- ------------------------------------------------------------
--- Last Modified On         : Mon  D, YEAR
--- Last Change Description  : [ LAST CHANGE DESCRIPTION ]
---
--- [ EXECUTION CODE ]
--- [dbo].[usp_YourSPName]
---
--- [ RELATED TABLES ]
--- SELECT * FROM  
---
---
CREATE PROCEDURE [dbo].[usp_YourSPName] 
AS
BEGIN  
      -- YOUR QUERY
END

/*
Object: Stored Procedure
Author: Ozesh Adonai Zaagan
Last Updated: August 12, 2019
Description: One super cool stored procedure
*/
CREATE PROCEDURE [dbo].[usp_YourSPName] 
AS
BEGIN  
      -- YOUR QUERY
END

CREATE PROCEDURE [dbo].[usp_SamplePaging] 
         @offset INT,
         @limit  INT
AS
  BEGIN
      WITH CurrentTable
           AS (SELECT *,
                      Row_number() OVER
                      ( ORDER BY LC.Name ASC ) AS RowNum
               FROM  dbo.LengthyTable LC),
           rowTotal ( RowTotal )
           AS (SELECT CONVERT(INT, Max(RowNum))
               FROM   CurrentTable)
      SELECT *
      FROM   CurrentTable
      WHERE  RowNum >= @offset
             AND RowNum <= ( @offset + @limit - 1 );
  END; 

-- Offset starting from 1.
-- This kind of paging returns one extra row 
-- such that in mobile apps, 
-- we can automatically handle the scroll event 
-- in cases where the record contains extra rows

-- For example: If we asked for 10 records and 
-- the script returns 11, We can write code to fetch 
-- the next 10 records if the last returned list
-- has one extra data.

-- Note : We always need to hide the extra rows that 
-- the script returns. 
-- Example : If the script returns 11 when 10 was asked, 
-- hide the last 11th record.

-- That last row is applicable to trigger the next fetch 
-- when the user reaches the end of the page
-- and needs to scroll further

CREATE PROCEDURE [dbo].[usp_SampleSPToSearchUsers]
    @offset INT ,
    @limit INT ,
    @Name NVARCHAR(250)
AS
    BEGIN
        SELECT  * ,
                ROW_NUMBER() OVER 
                 ( ORDER BY ( [UserID] ) DESC ) AS RowNum
        FROM    SampleUsersTable
        WHERE   FirstName + ' ' + LastName LIKE '%' + @Name + '%'
        ORDER BY ( [UserID] ) DESC
                OFFSET ( @offset - 1 ) ROWS 
                FETCH NEXT ( @limit + 1 ) ROWS
                ONLY;
    END;

DECLARE @Total BIGINT= 0;
SELECT  @Total = COUNT(1)
FROM    Table_Name;

SELECT  ROW_NUMBER() OVER 
        ( ORDER BY ( ColumnName ) ASC ) [RowNum] ,
        @Total [Total] ,
        *
FROM    Table_Name
ORDER BY ColumnName ASC
        OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

SELECT schemaname, relname, pg_size_pretty(pg_relation_size(relname::regclass)) relsize, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
  FROM pg_stat_user_tables 
 WHERE greatest(last_autoanalyze,last_analyze) < now()-interval '5d' 
   AND pg_relation_size(relname::regclass) > 10e6;

mysqldump --force --routines --events -E --hex-blob   --max_allowed_packet=1G   --set-gtid-purged=OFF --triggers --opt --add-drop-database  --user=root -h 192.168.2.33 -p --all-databases > /home/kaveh/Desktop/db.sql

SET FOREIGN_KEY_CHECKS=0;

SELECT
  Concat('TRUNCATE TABLE ', TABLE_NAME, ';')
FROM
  INFORMATION_SCHEMA.TABLES
WHERE
  table_schema = 'SCHEMA_NAME';

--Truncate all

SET FOREIGN_KEY_CHECKS=1;

-- Example 1
-- CTE Name and Column List
WITH Sales_CTE (SalesPersonID,SalesOrderID, SalesYear)
AS
-- CTE Query
(
    SELECT SalesPersonID,
           SalesOrderID,
           YEAR(OrderDate) As SalesYear
    FROM Sales.SalesOrderHeader
	  WHERE SalesPersonID IS NOT NULL
)
-- Outer Query
SELECT SC.SalesPersonID,
       COUNT(SC.SalesOrderID) AS TotalSales,
	   SC.SalesYear
FROM Sales_CTE AS SC
GROUP BY SC.SalesYear,SC.SalesPersonID
ORDER BY SC.SalesPersonID, SC.SalesYear;

-- Example 2
WITH Sales_CTE (SalesPersonID, NumberOfOrders)  
AS  
(  
    SELECT SalesPersonID, COUNT(*)  
    FROM Sales.SalesOrderHeader  
    WHERE SalesPersonID IS NOT NULL  
    GROUP BY SalesPersonID  
)  
SELECT AVG(NumberOfOrders) AS "Average Sales Per Person"  
FROM Sales_CTE;

-- Simple Select
SELECT 'Bananas';

-- Fully Qualified Name (Four Part)
SELECT * FROM Server.Database.Schema.Object;

-- Basic Syntax
SELECT [ All | Distinct ]
     [ TOP (Exp) [Percent] [With Ties]]
   * | {tbl | view | alias}.*  
     | {tbl | view | alias}.column
     | expression  
     [ [ AS ] column_alias ]  
     [ INTO new_source ]
     [ FROM source [, ...n] ]
     [ WHERE condition ]
     [ GROUP BY ]
     [ Having condition ]
     [ Order By exp ]
     [ Option options [,...n] ]

-- Examples >>

-- All
SELECT * FROM Person.Address;

-- Table Aliasing
SELECT addr.* FROM Person.Address addr;

-- Top
SELECT TOP(5) * FROM Sales.Customer;

-- Order By, Desc
SELECT ContactTypeID, Name
    FROM Person.ContactType
    ORDER BY Name DESC;
    
-- Distinct
SELECT DISTINCT Color 
    FROM Production.Product;

-- OVER
SELECT SalesOrderID, ProductID, OrderQty  
    ,SUM(OrderQty) 
        OVER(PARTITION BY 
                    SalesOrderID) AS Total  
    ,AVG(OrderQty) 
        OVER(PARTITION BY 
                    SalesOrderID) AS "Avg"  
    ,COUNT(OrderQty)
        OVER(PARTITION BY 
                    SalesOrderID) AS "Count"  
    ,MIN(OrderQty) 
        OVER(PARTITION BY 
                    SalesOrderID) AS "Min"  
    ,MAX(OrderQty) 
        OVER(PARTITION BY 
                    SalesOrderID) AS "Max"  
FROM Sales.SalesOrderDetail   
WHERE SalesOrderID IN(43659,43664);

-- Having
SELECT SalesOrderID, 
       SUM(LineTotal) AS SubTotal  
FROM Sales.SalesOrderDetail  
GROUP BY SalesOrderID  
HAVING SUM(LineTotal) > 100000.00  
ORDER BY SalesOrderID ; 

SELECT *
  FROM
    tables(s)
    subqueries (as derived tables) AS NamedRangeAlias
    common table expressions (CTE)
    views
    table-valued user-defined functions
    distributed data sources (OpenQuery) 
    XML data sources (XQuery)