SQL Training: Exercises on Stored Procedures and Functions

Exercises on Stored Procedures and Functions:

1. Create a function that should take two inputs those are product id and location id and should
return stock level.
Note: Consider product Inventory table in adventurworks database for this requirement.

2. Create a function that should take input as store id and returns the columns ProductID, Name,
and the aggregate of line total for each product sold to the store.
Note: consider product, sales order details, sales order header and customer tables for this
requirements.

3. Someone deleted few records from a table which has an IDENTITY column, so need to create
stored procedures to find missing sequential numbers.
Note: for this requirement create sample table with identity column and insert some data and
delete few records from table.

4. Sales order header table in adventureworks database as follows
Requirement:
Need to create stored procedure that should take sortdate as argument (varchar datatype) then it
should display result set with columns salesorderid, revisionnumber,orderdate,duedate,dhipdate
If we pass orderdate column name as argument to the stored procedure it should sort result set
based on ordedate
If we pass duedate column name as argument to the stored procedure it should sort result set based
on duedate
If we pass shipdate column name as argument to the stored procedure it should sort result set
based on shipdate.

5. There are four seasons in year as follows Spring (March, April, May) Summer (June, July, August)
Autumn (September, October, November) Winter (December, January, February)
Consider orderdate column in salesorderheader table (adventureworks)
Requirement:
Need to Create a stored procedure to display result as follows

6. Create stored procedure to display list of views created on particular table
Note: Need to pass table name as argument to the stored procedure.

7. Create a Stored procedure to display store details and contact details
Note : consider [Store], [BusinessEntityContact], [ContactType], [Person], [EmailAddress],
[PersonPhone] tables from adventureworks database for this requirement.

8. Create a stored procedure to display employee details by taking business entity id as argument.

9. Need to create stored procedure that should take start date as argument and return no of
working days in current month except Sundays starting from start data.

10. Need to create stored procedure that should take start date and end date as arguments and
return no working days (except Saturday and Sunday).

MY SOLUTIONS:

----------------------
-----------------------
--EXERCISES ON STORED PROCEDURES

--Q1
CREATE FUNCTION Udf_GetStockLevel(@ProductID AS INT,@locationID AS SMALLINT)
RETURNS TABLE
RETURN
  SELECT Quantity
   FROM  Production.ProductInventory
  WHERE ProductID = @ProductID AND LocationID = @locationID

--SELECT * FROM Udf_GetStockLevel(1,6)

--Q2
CREATE FUNCTION Udf_GetProductInfo(@storeID AS INT)
RETURNS TABLE
RETURN
SELECT p.ProductID
      ,p.Name
  ,sod.LineTotal
  FROM Sales.SalesOrderDetail sod
INNER JOIN Sales.SalesOrderHeader soh
       ON sod.SalesOrderID = soh.SalesOrderID
INNER JOIN Production.Product p
       ON sod.ProductID = p.ProductID
INNER JOIN Sales.Customer c
       ON soh.CustomerID = c.CustomerID
WHERE c.StoreID = @storeID

--SELECT * FROM Udf_GetProductInfo(1028)

--Q3
CREATE TABLE TEST_DEL(ID INT PRIMARY KEY IDENTITY(1,1),NAME NVARCHAR(25))
INSERT TEST_DEL VALUES('A')
INSERT TEST_DEL VALUES('A')
INSERT TEST_DEL VALUES('B')
INSERT TEST_DEL VALUES('C')
INSERT TEST_DEL VALUES('D')
INSERT TEST_DEL VALUES('E')

DELETE FROM TEST_DEL WHERE NAME IN ( 'A','D')

CREATE PROCEDURE usp_FindMissingNo
AS
BEGIN
 DECLARE @minId AS INT,@maxId AS INT
  SELECT @minId = 1,@maxId = MAX(ID) FROM TEST_DEL

       CREATE TABLE #temp(MISSINGNO INT)

WHILE(@minId < @maxId)
  BEGIN
   
INSERT INTO #temp
SELECT X.MISSINGNO
   FROM(
SELECT @minId AS MISSINGNO
EXCEPT
SELECT ID
   FROM TEST_DEL
WHERE ID = @minId
)X

SET @minId = @minId + 1
  END

SELECT * FROM #temp
DROP TABLE #temp
END

--EXEC usp_FindMissingNo

--Q4
CREATE PROCEDURE usp_sortbyparam(@sortDate VARCHAR(20))
AS
BEGIN

  IF(@sortDate = 'OrderDate')
    SELECT SalesOrderID
  ,RevisionNumber
  ,OrderDate
  ,DueDate
  ,ShipDate
     FROM Sales.SalesOrderHeader
ORDER BY OrderDate

  IF(@sortDate = 'DueDate')
SELECT SalesOrderID
  ,RevisionNumber
  ,OrderDate
  ,DueDate
  ,ShipDate
     FROM Sales.SalesOrderHeader
ORDER BY DueDate

  IF(@sortDate = 'ShipDate')
SELECT SalesOrderID
  ,RevisionNumber
  ,OrderDate
  ,DueDate
  ,ShipDate
     FROM Sales.SalesOrderHeader
ORDER BY ShipDate
END
--EXEC usp_sortbyparam 'ShipDate'

--Q5
CREATE PROCEDURE usp_giveseasonnametoordermonth
AS
BEGIN

    SELECT DISTINCT DATENAME(MONTH,OrderDate) AS ORDERMONTH,
                CASE WHEN DATENAME(MONTH,OrderDate) IN ('March','April','May') THEN 'Spring'
     WHEN DATENAME(MONTH,OrderDate) IN ('June','July','August')  THEN 'Summer'
     WHEN DATENAME(MONTH,OrderDate) IN ('September','October','November')  THEN 'Autumn'
     WHEN DATENAME(MONTH,OrderDate) IN ('December','January','February')  THEN 'Winter'
END AS SEASON
     FROM Sales.SalesOrderHeader

END
--EXEC usp_giveseasonnametoordermonth

--Q6
CREATE PROCEDURE usp_getViewsListOnGivenTable(@tableName VARCHAR(100))
AS
BEGIN
SELECT VIEW_CATALOG
  ,VIEW_SCHEMA
  ,VIEW_NAME
  ,TABLE_CATALOG
  ,TABLE_SCHEMA
  ,TABLE_NAME
  FROM [INFORMATION_SCHEMA].[VIEW_TABLE_USAGE]
WHERE TABLE_NAME = @tableName
END
--EXEC usp_getViewsListOnGivenTable 'Address'

--Q7
CREATE PROCEDURE usp_getStoreAndPersonInfo(@tableName VARCHAR(100))
AS
BEGIN
SELECT s.[Name]
  ,s.[SalesPersonID]
  ,p.FirstName
  ,p.LastName
  ,ph.PhoneNumber
  ,em.EmailAddress
  ,ct.Name
  FROM [Sales].[Store] s
  INNER JOIN Person.Person p
ON s.BusinessEntityID = p.BusinessEntityID
  INNER JOIN Person.PersonPhone ph
ON p.BusinessEntityID = ph.BusinessEntityID
  INNER JOIN Person.EmailAddress em
ON p.BusinessEntityID = em.BusinessEntityID
  INNER JOIN Person.BusinessEntityContact bec
ON p.BusinessEntityID = bec.BusinessEntityID
  INNER JOIN Person.ContactType ct
ON bec.ContactTypeID = ct.ContactTypeID
END

--EXEC usp_getStoreAndPersonInfo

--Q8
CREATE PROCEDURE usp_getEmpDetails(@businessEntityID INT)
AS
BEGIN
SELECT BusinessEntityID
  ,NationalIDNumber
  ,LoginID
  ,OrganizationNode
  ,OrganizationLevel
  ,JobTitle
  ,BirthDate
  ,MaritalStatus
  ,Gender
  ,HireDate
  ,VacationHours
  ,SickLeaveHours
  FROM HumanResources.Employee
WHERE BusinessEntityID = @businessEntityID
END
--EXEC usp_getEmpDetails 1

--Q9
CREATE PROCEDURE usp_getNoOfWorkingDays(@startDate DATE)
AS
BEGIN
DECLARE @lastDayInCurrMonth DATE

SELECT @lastDayInCurrMonth = EOMONTH(GETDATE())

    SELECT
        --Start with total number of days including weekends
        (DATEDIFF(dd,@startDate, @lastDayInCurrMonth)+1)
        --Subtact 1 day for each full weekend
        -(DATEDIFF(wk,@startDate, @lastDayInCurrMonth)*1)
        --If StartDate is a Sunday, Subtract 1
        -(CASE WHEN DATENAME(dw, @startDate) = 'Sunday'
            THEN 1
            ELSE 0
        END) AS NOOFWORKINGDAYS
 END
 --EXEC usp_getNoOfWorkingDays '2/14/2017'

 --Q10
 CREATE PROCEDURE usp_getNoOfWorkingDaysforanenddate(@startDate DATE,@endDate DATE)
AS
BEGIN

    SELECT
        --Start with total number of days including weekends
        (DATEDIFF(dd,@startDate, @endDate)+1)
        --Subtact 2 days for each full weekend
        -(DATEDIFF(wk,@startDate, @endDate)*2)
        --If StartDate is a Sunday, Subtract 1
        -(CASE WHEN DATENAME(dw, @startDate) = 'Sunday'
            THEN 1
            ELSE 0
        END)
-(CASE WHEN DATENAME(dw, @startDate) = 'Saturday'
            THEN 1
            ELSE 0
        END) AS NOOFWORKINGDAYS
 END

 --EXEC usp_getNoOfWorkingDaysforanenddate '2/14/2017','2/20/2017'