Wednesday, September 15, 2010

Error Handling

Error Handling (Exception Handling) is one the of key strategy one should think of.
SQL Server 2005 has introduced sophisticated Error Handling approach with TRY-CATCH block which was not available in prior versions.

Here is the approach which can be used as Best practices.

TRY-CATCH BLOCK

  • In SP, we should put all our logic in TRY block and CATCH block can be utilized for throwing an error
  • Inside TRY block, we can open a transaction and same can be COMMITed at the end
  • CATCH block should be used to ROLLBACK a transaction
  • New in-built functions have been created in SQL Server 2005 like ERROR_NUMBER(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_MESSAGE() which are specially for CATCH block.
  • Every TRY block should be ending CATCH block. 1 TRY can have only 1 corresponding CATCH block
  • Nesting is allowed means one TRY-CATCH block can be part of outer TRY block.

DESIGN


  • One Table needs to be created to capture and store all the errors.
  • One master SP needs to be created which captures the log in above table. This SP will be kept under CATCH block of all the SPs
  • while using RAISERROR , use WITH LOG. This will log in errorlog also.
DESIGN CODE


CREATE TABLE [ErrorLog](
      [ErrorLogID] [int] IDENTITY(1,1) NOT NULL,
      [ErrorDescription] [nvarchar](max) NOT NULL,
      [ErrorSourceName] [varchar](255) NOT NULL,
      [SysMessagesErrorNumber] [int] NOT NULL,
      [ErrorSourceLineNumber] [smallint] NOT NULL,
      [IsDeleted] [bit] NOT NULL DEFAULT 0,
      [CreatedBy] [int] NOT NULL,
      [CreatedDateTime] [datetime] NOT NULL DEFAULT GETUTCDATE(),
 CONSTRAINT [PK_ErrorLog] PRIMARY KEY CLUSTERED
(
      [ErrorLogID] ASC
)
)


CREATE PROCEDURE [uspThrow_Error]
(
      @userProfileId INT=NULL
)
AS
BEGIN
      -- Return if there is no error information to retrieve.
      IF ERROR_NUMBER() IS NULL 
            RETURN;

      DECLARE
            @errorMessage    NVARCHAR(MAX),
            @errorNumber     INT,
            @errorSeverity   INT,
            @errorState      INT,
            @errorLine       INT,
            @errorProcedure  NVARCHAR(200)

      -- Assign variables to error-handling functions that
      -- capture information for RAISERROR.
      SELECT
            @errorNumber = ERROR_NUMBER(),
            @errorSeverity = ERROR_SEVERITY(),
            @errorState = ERROR_STATE(),
            @errorLine = ERROR_LINE(),
            @errorMessage = ERROR_MESSAGE();

      -- Capturing procedure name along with Schema
      SET @errorProcedure = ISNULL(Library.ufnGet_FullObjectName(ERROR_PROCEDURE(),1),'')

      -- This is some technical error. So log into ErrorLog table
      INSERT INTO Library.ErrorLog
      (
            SysMessagesErrorNumber, ErrorSourceName, ErrorSourceLineNumber, ErrorDescription, CreatedBy, CreatedDateTime
      )
      VALUES
      (
            @errorNumber,@errorProcedure, @errorLine, @errorMessage, ISNULL(@userProfileId,1), GETUTCDATE()
      )

      -- Now Raise an error. This time it will take Error number as 50000. So Front-end will show a generic Error page.
      RAISERROR
      (
            @errorMessage,
            @errorSeverity,
            1,
            @errorProcedure,
            @errorNumber,    -- parameter: original error number.
            @errorSeverity,  -- parameter: original error severity.
            @errorState,     -- parameter: original error state.
            @errorProcedure, -- parameter: original error procedure name.
            @errorLine       -- parameter: original error line number.
      ) WITH LOG;

END
GO

SAMPLE CODE

CREATE PROC SampleProc
AS
BEGIN
      BEGIN TRY
            BEGIN TRAN
                  -- Your code
                  SELECT 1/0
            COMMIT
      END TRY
      BEGIN CATCH
            EXEC [uspThrow_Error]
      END CATCH
END




    Sunday, September 12, 2010

    T-SQL Queries


    1. EMPLOYEE


    --CREATING TABLE
    CREATE TABLE Employee (EID INT PRIMARY KEY, EName VARCHAR(10), Salary INT)

    --INSERTING RECORDS
    INSERT INTO Employee (EID, EName, Salary)
    VALUES (1, 'John', 20000)
    ,(2, 'Rob', 2000)
    ,(3, 'Bob', 10000)
    ,(4, 'Robert', 20300)
    ,(5, 'Bruce', 40000)
    ,(6, 'Williams', 5000)
    ,(7, 'Megan', 900)
    ,(8, 'Steven', 9000)
    ,(9, 'George', 12500)
    ,(10, 'Alex', 14000)

    A.) List employees who are earning more than 10000

    SELECT  EName
    FROM    Employee
    WHERE   Salary > 10000

    B.) List employees whose name starts with ‘R’

    SELECT  EName
    FROM    Employee
    WHERE   EName LIKE 'R%'

    C.) List employees whose length of the name is more than 4

    SELECT  EName
    FROM    Employee
    WHERE   LEN(EName) > 4

    D.) List employees who are earning between 8000 and 20000 and whose name starts with G or A

    SELECT  EName
    FROM    Employee
    WHERE   Salary BETWEEN 8000 AND 20000
            AND (EName LIKE 'G%' OR EName LIKE 'A%')

    E.) List employees who born in 1985

    SELECT  EName
    FROM    Employee
    WHERE   DATEPART(YYYY,DOB) = 1985

    F.) List employees who born in March moth

    -- Option : 1
    SELECT  EName
    FROM    Employee
    WHERE   DATEPART(MM,DOB) = 3 -- MARCH

    -- Option : 2
    SELECT  EName
    FROM    Employee
    WHERE   DATENAME(MM,DOB) = 'March'


    G.) List employees who born after 1990 or earning more than 20000

    SELECT  EName
    FROM    Employee
    WHERE   DOB > '1990-12-31'

            OR Salary > 20000

    -------------------------------------------------------------------------------------------


    2. EMPLOYEE - PHONE


    --CREATING TABLE
    CREATE TABLE Phones (PhoneID INT PRIMARY KEY IDENTITY, EID INT, Phone VARCHAR(10) )

    -- INSERTING RECORDS

    INSERT INTO Phones (EID, Phone)
    VALUES
     (1, '123456789')
    ,(2,'4597861356')
    ,(3,'6556454656')
    ,(2,'7878979899')
    ,(6,'7845454656')
    ,(1,'1235465654')
    ,(3,'5446556656')
    ,(4,'1215455465')
    ,(4,'7894521656')
    ,(7,'5464212664')
    ,(5,'6564646565')
    ,(5,'7854554546')
    ,(4,'1244544794')


    A.) List of employees who has at least one phone

    -- Option : 1
    SELECT  EName
    FROM    Employee
    WHERE   EID IN (SELECT EID FROM Phones)

    -- Option : 2
    SELECT  EName
    FROM    Employee E
    WHERE   EXISTS (SELECT 1 FROM Phones P WHERE E.EID = P.EID)

    -- Option : 3
    SELECT  DISTINCT EName
    FROM    Employee E
            INNER JOIN Phones P ON E.EID = P.EID

    B.) List of employees who does not have phone

    -- Option : 1
    SELECT  EName
    FROM    Employee
    WHERE   EID NOT IN (SELECT EID FROM Phones)

    -- Option : 2
    SELECT  EName
    FROM    Employee E
    WHERE   NOT EXISTS (SELECT 1 FROM Phones P WHERE E.EID = P.EID)

    -- Option : 3
    SELECT  EName
    FROM    Employee E
            LEFT JOIN Phones P ON E.EID = P.EID
    WHERE   P.EID IS NULL


    C.) List of total number of phones per employee. If employee does not have phone then display 0

    -- Option : 1
    SELECT  E.EID, EName, ISNULL(TotalPhones,0) TotalPhones
    FROM    Employee E
            LEFT JOIN
            (
                SELECT  EID, COUNT(Phone) TotalPhones
                FROM    Phones P
                GROUP BY    EID
            ) X ON E.EID = X.EID

    -- Option : 2
    SELECT  E.EID, EName, ISNULL(COUNT(Phone),0) TotalPhones
    FROM    Employee E
            LEFT JOIN Phones P ON E.EID = P.EID
    GROUP BY    E.EID, EName

    D.) List employees (who born after 1985) with total number of phones. If employee does not have phone then it should display 0

    SELECT  E.EID, EName, DOB, ISNULL(COUNT(Phone),0) TotalPhones
    FROM    Employee E
            LEFT JOIN Phones P ON E.EID = P.EID
    WHERE   DOB > '1985-12-31'
    GROUP BY    E.EID, EName, DOB

    -----------------------------------------------------------------------------------------------------


    3. CRICKETER - MANAGER


    -- Creating table
    CREATE TABLE Cricker (CID INT IDENTITY(1,1), CName VARCHAR(10), ManagerID INT)

    -- Inserting records

    INSERT INTO Cricker (CName, ManagerID)
    VALUES ('Dhoni',NULL)
    ,('Sehwag',1)
    ,('Raina',1)
    ,('Virat',2)
    ,('Yuvi',3)
    ,('Yusuf',2)
    ,('Zaheer',1)
    ,('Bhajji',7)
    ,('Nehra',7)
    ,('Ashwin',8)
    ,('Vijay',2)


    A.) List of cricketers along with their respective manager. If cricketer does not have manager then ‘No Manager’ should be displayed

    SELECT  C.CName, ISNULL(M.CName,'No Manager') ManagerName
    FROM    Cricker C
            LEFT JOIN Cricker M ON C.ManagerID = M.CID

    B.) List of cricketers along with total number of direct subordinates. If no subordinate then 0 should be displayed

    SELECT  C.CName, ISNULL(TotalSubordinates ,0) TotalSubordinates
    FROM    Cricker C
            LEFT JOIN   (
                            SELECT  ManagerID, COUNT(*) TotalSubordinates
                            FROM    Cricker
                            GROUP BY ManagerID
                  ) X ON C.CID = X.ManagerID

    C.) List of cricketer(s) who has/have highest number of direct subordinates

    CREATE VIEW vwCricketerSubordinates
    AS
        SELECT  ManagerID , COUNT(*) TotalSubordinates
        FROM      Cricker
        GROUP BY ManagerID
    Go

    SELECT  CName
    FROM    vwCricketerSubordinates V
            INNER JOIN Cricker C ON V.ManagerID = C.CID
    WHERE   TotalSubordinates = (
                                    SELECT MAX(TotalSubordinates)
                                    FROM vwCricketerSubordinates
                            )

    ------------------------------------------------------------

    GET THE FIRST DAY OF THE MONTH

    SELECT DATENAME(DW, DATEADD(DD,-(DATEPART(DD,GETDATE()))+1, GETDATE()))

    GET COUNTRIES IN SORTED ORDER WITH INDIA AS FIRST COUNTRY


    SELECT * FROM COUNTRY ORDER BY CASE WHEN CountryName = 'India' THEN '-1' ELSE CountryName END


    CHANGE GENDER (REPLACE "Male" WITH "Female" AND VICE VERSA)


    UPDATE UserProfile SET Gender = CASE WHEN 'Male' THEN 'Female' WHEN 'Female' THEN 'Male' ELSE NULL END


    GIVE THE RANK BASED ON TOTAL MARK






    Using Rank Function


    Using Co-related Subquery
    Result
    Now, give the rank in alphabetical order in case of same TotalMarks


    REMOVE DUPLICATES FROM A TABLE

    CREATE TABLE DuplicateCheck (a int, b varchar(10))


    INSERT INTO DuplicateCheck (a,b) VALUES
    (1,'a'),(1,'a'),(1,'a'),
    (2,'b'),(2,'b'),
    (3,'c'),(3,'c'),
    (4,'d')

    -- APPROACH 1 (USING Identity COLUMN, co-related query)
    ALTER TABLE DuplicateCheck ADD IdentityColumn INT IDENTITY

    SELECT * FROM DuplicateCheck

    DELETE      DC
    FROM  DuplicateCheck DC
    WHERE NOT EXISTS
                (
                      SELECT      1
                      FROM  (SELECT MIN(D.IdentityColumn) MinIdentityColumn,A,B FROM DuplicateCheck D GROUP BY A,B) X
                      WHERE DC.IdentityColumn = X.MinIdentityColumn
                )

    ALTER TABLE DuplicateCheck DROP COLUMN IdentityColumn

    SELECT * FROM DuplicateCheck

    -- APPROACH 2 (USING TEMP table)

    TRUNCATE TABLE DuplicateCheck
    INSERT INTO DuplicateCheck (a,b) VALUES
    (1,'a'),(1,'a'),(1,'a'),
    (2,'b'),(2,'b'),
    (3,'c'),(3,'c'),
    (4,'d')

    CREATE TABLE #TEMP (A INT, B VARCHAR(10))

    INSERT INTO #TEMP (A,B) SELECT DISTINCT A,B FROM DuplicateCheck

    TRUNCATE TABLE DuplicateCheck
    INSERT INTO DuplicateCheck (A,B) SELECT DISTINCT A,B FROM #TEMP

    DROP TABLE #TEMP
    SELECT * FROM DuplicateCheck


    -- APPROACH 3 (USING Identity column, SELF JOIN)

    TRUNCATE TABLE DuplicateCheck
    INSERT INTO DuplicateCheck (a,b) VALUES
    (1,'a'),(1,'a'),(1,'a'),
    (2,'b'),(2,'b'),
    (3,'c'),(3,'c'),
    (4,'d')

    ALTER TABLE DuplicateCheck ADD IdentityColumn INT IDENTITY

    SELECT * FROM DuplicateCheck

    DELETE      DCMain
    FROM  DuplicateCheck DCMain
                INNER JOIN DuplicateCheck DCRunning
                      ON DCMain.a = DCRunning.a
                      AND DCMain.b = DCRunning.b
                      AND DCMain.IdentityColumn > DCRunning.IdentityColumn

    ALTER TABLE DuplicateCheck DROP COLUMN IdentityColumn
                     
    SELECT * FROM DuplicateCheck

    -- APPROACH 4 (USING CTE)

    TRUNCATE TABLE DuplicateCheck
    INSERT INTO DuplicateCheck (a,b) VALUES
    (1,'a'),(1,'a'),(1,'a'),
    (2,'b'),(2,'b'),
    (3,'c'),(3,'c'),
    (4,'d')

    WITH del (A,B,DuplicateCount)
    AS
    (
          SELECT A,B,ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY A) DuplicateCount FROM DuplicateCheck
    )
    DELETE FROM del WHERE DuplicateCount > 1

    SELECT * FROM DuplicateCheck
                     

    FIND FACTORIAL





    -- APPROACH 1 (Recursive Function)

    CREATE FUNCTION Facto (@number INT)
    RETURNS DECIMAL(38,0)
    BEGIN
          DECLARE @result DECIMAL(38,0)
          SET @result = @number
         
          IF @number = 0
                SET @result = 1
          ELSE
                SET @result = @result * dbo.Facto(@number-1)
         
          RETURN @result
    END

    select dbo.Facto(-4)


    -- APPROACH 2 (CTE)

    CREATE FUNCTION FactCTE (@number INT)
    RETURNS DECIMAL(38,0)
    BEGIN
          DECLARE @result DECIMAL(38,0);
          WITH CTE AS
          (
                SELECT 0 number, CONVERT(DECIMAL(38,0),1) FactResult
                UNION ALL
                SELECT number+1, FactResult * (number+1) FROM CTE WHERE number < @number
          )
          SELECT @result = FactResult FROM CTE
         
          RETURN @result
    END


    select dbo.FactCTE(4)