nth Highest Salary in SQL Server 2005

IF OBJECT_ID('tempdb..#salary') IS NOT NULL
    BEGIN
    DROP TABLE #salary
    END
 
CREATE TABLE #salary
    (
    Salary      INT
    )
 
INSERT INTO #salary(Salary) VALUES(5) -- 1
INSERT INTO #salary(Salary) VALUES(4) -- 2
INSERT INTO #salary(Salary) VALUES(3) -- 3
INSERT INTO #salary(Salary) VALUES(3) -- 3
INSERT INTO #salary(Salary) VALUES(2) -- 4
INSERT INTO #salary(Salary) VALUES(1) -- 5

-- SALARY RANK
SELECT  SalaryRank = (SELECT COUNT(DISTINCT Salary) FROM #salary b WHERE b.Salary > a.Salary)+1 ,
        Salary
FROM    #salary a
ORDER
BY      1
 
DECLARE @salaryRank INT
SET     @salaryRank = 4
 

SET     @salaryRank = 4 -- RESULT SHOULD BE 2
-- WRONG
SELECT salary FROM #salary a 
    WHERE (@salaryRank - 1) = (SELECT COUNT(salary) FROM #salary b WHERE b.salary > a.salary)
/* RESULT
salary
-----------
*/
-- CORRECT
SELECT  TOP 1
        Salary
FROM    #salary a
WHERE   (@salaryRank - 1) = (SELECT COUNT(DISTINCT Salary) FROM #salary b WHERE b.Salary > a.Salary)
/* RESULT
Salary
-----------
2
*/
 
SET     @salaryRank = 5 -- RESULT SHOULD BE 1
-- WRONG
SELECT salary FROM #salary a
    WHERE (@salaryRank - 1) = (SELECT COUNT(salary) FROM #salary b WHERE b.salary > a.salary)
/* RESULT
salary
-----------
2
*/
-- CORRECT
SELECT  TOP 1
        Salary
FROM    #salary a
WHERE   (@salaryRank - 1) = (SELECT COUNT(DISTINCT Salary) FROM #salary b WHERE b.Salary > a.Salary)
/* RESULT
Salary
-----------
1
*/

Please note that SELECT COUNT(salary) FROM #salary b ... is replaced with SELECT COUNT(DISTINCTSalary) FROM #salary b
 
Another solution to get the N-th highest/lowest salary is given below:
 

 Collapse | Copy Code

SET     @salaryRank = 5 -- RESULT SHOULD BE 1
-- N-th highest salary
SELECT  TOP 1
        Salary
FROM    (
        SELECT  SalaryRank  = DENSE_RANK() OVER(ORDER BY Salary DESC),
                Salary
        FROM    #salary
        ) a
WHERE   SalaryRank = @salaryRank
-- N-th lowest salary -- RESULT SHOULD BE 5
SELECT  TOP 1
        Salary
FROM    (
        SELECT  SalaryRank  = DENSE_RANK() OVER(ORDER BY Salary),
                Salary
        FROM    #salary
        ) a
WHERE   SalaryRank = @salaryRank

Table Value Parameter in SQL Server 2008

Introduction

One of the fantastic new features of SQL Server 2008 is the Table value parameter. In previous versions of SQL Server, there wasn’t a native way to pass a table to a Stored Procedure or functions. The usual workaround was to pass a large varchar or the XML type and parse through it. Now, in SQL Server 2008, Table parameters are available.

Many a times, we need to utilize a single Stored Procedure to update multiple database tables with one-to-many relationships. In such occasions, we end up concatenating large strings, and inside the procedure, end up parsing the string to get records. With SQL Server 2008, it is very simple, and we no more need to write tons of lines of code to implement such requirements.

Facts about Table Value Parameters

Here are a few facts that you must know before starting to use the Table Value Parameter:

1. Helps address the need to pass an “array” of elements to a Stored Procedure / function.
2. Table-valued parameters are used to send multiple rows of data to a Transact-SQL statement or a routine, such as a Stored Procedure or function, without creating a temporary table or many parameters.
3. Overcomes both security and performance issues in a dynamic SQL approach. Internally treated like a table variable.
4. Scope is batch.
5. Not affected by rollback (beyond the atomic statement scope).
6. No histograms/distribution statistics.
7. When parameter value not provided, defaults to empty table.

Example

 Collapse | Copy Code

CREATE TYPE dbo.OrderIDs AS TABLE 
( 
  pos INT NOT NULL PRIMARY KEY,
  orderid INT NOT NULL UNIQUE
)

DECLARE @T AS dbo.OrderIDs;

INSERT INTO @T(pos, orderid) VALUES(1, 10248)
INSERT INTO @T(pos, orderid) VALUES(2, 10250)
INSERT INTO @T(pos, orderid) VALUES(3, 10249);

SELECT * FROM @T;

In the above code snippet, we create a Table User Defined Type. In SQL Server 2008, we can define Table UDTs (this is a new feature in SQL Server 2008). Then, create a variable using the newly created UDT and insert a few records into the variable and query the same.

 Collapse | Copy Code

CREATE PROC dbo.sp_GetOrders
(@T AS dbo.tbl_Order READONLY)
AS
SELECT O.OrderID, O.OrderDate, O.CustomerID
FROM dbo.tbl_Order AS O JOIN @T AS T ON 
    O.OrderID = T. OrderID
ORDER BY T. RecordID;
GO

DECLARE @MyOrderIDs AS dbo.tbl_Order;
INSERT INTO @MyOrderIDs(pos, orderid) VALUES(1, 10248)
INSERT INTO @MyOrderIDs(pos, orderid) VALUES(2, 10250)
INSERT INTO @MyOrderIDs(pos, orderid) VALUES(3, 10249);

EXEC dbo.sp_GetOrders @T = @MyOrderIDs;

The above code snippet shows how to use the Table UDT as a parameter in SQL Stored Procedure.

Conclusion

So now, with the Table UDT and the ability to pass the table value parameter to Stored Procedures and functions, we need lesser code and also get performance benefits.

Hope you enjoyed this article. Happy programming!!!

Overview of Error Handling in SQL Server 2005

Table of Contents

• Introduction
• When We Need To Handle Error in SQL Server
• Error Handling Mechanism
• Using @@ERROR
  • General Syntax
  • Return Type
  • Sample Example 
  • When We Should Use @@Error
• Using Try...Catch Block
  • General Syntax
  • Sample Example
  • Nested TRY-CATCH Block
  • Try-Catch Block for Transaction Roll Back
• Future Study
• History

Introduction

During development of any application, one of the most common things we need to take care of is Exception and Error handling. Similarly we need to take care of handling error and exception while designing our database like inside stored procedure. In SQL Server 2005, there are some beautiful features available using which we can handle the error.

When We Need To Handle Error in SQL Server

Generally a developer tries to handle all kinds of exception from the code itself. But sometimes we need to handle the same from the DB site itself. There are some scenarios like, we are expecting some rows should come when we will execute the store procedure, but unfortunately SP returns none of them. Below points can be some possible scenarios where we can use error handling:

• While executing some DML Statement like INSERT, DELETE, UPDATE we can handle the error for checking proper output
• If transaction fails, then we need to rollback - This can be done by error handling
• While using Cursor in SQL Server

Error Handling Mechanism

The two most common mechanisms for error handling in SQL Server 2005 are:

• @@ERROR
• TRY-CATCH Block

Let's have a look at how we can implement both @@Error and Try-Catch block to handle the error in SQL Server 2005.

Using @@ERROR

We can consider @@ERROR as one of the basic error handling mechanisms in SQL Server. @@Error is a Global Variable in SQL Server. This variable automatically populates the error message when a certain error occurred in any statement. But we have to trace it within just after the next line where the actual error occurred, otherwise, it will reset to 0.

General Syntax

General syntax for @@ERROR is as follows:

 Collapse | Copy Code

Select @@ERROR

Return Type

 Collapse | Copy Code

int

It returns the Error Number.

Sample Example

I have a table named StudentDetails with columns, Roll (int), Name (varchar) and Address (varchar). Now I am intentionally trying to insert a char in Roll field:

 Collapse | Copy Code

insert into StudentDetails (roll,[Name],Address)
 values ('a','Abhijit','India')

This will throw the following Error :

 Collapse | Copy Code

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'a' to data type int.

Check out the Message and number, it is 245. Now, I am executing the @@Error statement just after this statement and check out the output:

 Collapse | Copy Code

Select  @@Error 

The output is:

So, @@Error returns the same error as return by insert command. As I have already said, @@Error returns the error number for the last Transact-SQL statement executed, so if we execute any @@Error statement, we will get output 0.

When We Should Use @@Error 

There are some scenarios where we should use @@ERROR:

• With Insert, Delete, Update, Select Into Statement
• While using Cursor in SQL Server (Open, Fetch Cursor)
• While executing any Stored Procedure

Using Try...Catch Block

This is available from SQL Server 2005 Onwards. This is generally used where want to trap or catch error for multiple SQL statements like or a SQL Block of statement. TRY...CATCH blocks are the standard approach to exception handling in modern programming languages. Use and syntax are likely the same as normal programming language. Like Exception Handling in Programming Language, we can use nested Try-Catch block in SQL Server also.

Try block will catch the error and will throw it in the Catch block. Catch block then handles the scenario.

General Syntax

Below is the general syntax for Try-Catch block:

 Collapse | Copy Code

-- SQL Statement
-- SQL Statement
BEGIN TRY
   -- SQL Statement or Block
END TRY
BEGIN CATCH
   -- SQL Statement or Block
END CATCH
-- SQL Statement

Whenever there are some errors in TRY Block, execution will moved to CATCH block.

Sample Example

As I have already discussed about the studentDetails table, I am now going to insert one record in the table withRoll='a'.

 Collapse | Copy Code

BEGIN TRY
     INSERT INTO StudentDetails(Roll, [Name])
     VALUES('a', 'Abhijit')
END TRY
BEGIN CATCH
   SELECT 'There was an error while  Inserting records in DB '
END CATCH

As Roll is an int type but I am trying to insert a char type data which will violate the type conversion rule, an error will be thrown. So the execution pointer will jump to Catch block. And below is the output:

 Collapse | Copy Code

There was an error while  Inserting records in DB

Now, to get the details of the error SQL Server provides the following System function that we can use inside ourCatch-block for retrieving the details of the error. Please check the below table:

Function Name	Description
ERROR_MESSAGE()	Returns the complete description of the error message
ERROR_NUMBER()	Returns the number of the error
ERROR_SEVERITY()	Returns the number of the Severity
ERROR_STATE()	Returns the error state number
ERROR_PROCEDURE()	Returns the name of the stored procedure where the error occurred
ERROR_LINE()	Returns the line number that caused the error

Here is one simple example of using System Function:

 Collapse | Copy Code

BEGIN TRY
   INSERT INTO StudentDetails(Roll, [Name])
    VALUES('a', 'Abhijit')
END TRY
BEGIN CATCH
   SELECT  ' Error Message: '  + ERROR_MESSAGE() as ErrorDescription
END CATCH

I have executed the same code block here but rather than showing custom message, I am showing the internal Error message by Calling ERROR_MESSAGE() System function. Below is the output:

Nested TRY-CATCH Block 

Like other programming languages, we can use Nested Try catch block in SQL Server 2005.

 Collapse | Copy Code

BEGIN TRY
  print 'At Outer Try Block'
   BEGIN TRY
      print 'At Inner Try Block'
   END TRY
   BEGIN CATCH
      print 'At Inner catch Block'
   END CATCH
END TRY
BEGIN CATCH
   print 'At Outer catch block'
END CATCH

If we execute this, the output will look like:

 Collapse | Copy Code

At Outer Try Block
At Inner Try Block

Now, Inner catch blocks throw an error:

 Collapse | Copy Code

BEGIN TRY
  print 'At Outer Try Block'
   BEGIN TRY
      print 'At Inner Try Block'
      INSERT INTO StudentDetails(Roll, [Name])   _
  VALUES('a', 'Abhijit')  -- Throwing Exception
   END TRY
   BEGIN CATCH
      print 'At Inner catch Block'
   END CATCH
END TRY
BEGIN CATCH
   print 'At Outer catch block'
END CATCH

Which gives the following output:

 Collapse | Copy Code

At Outer Try Block
At Inner Try Block
At Inner catch Block

Try-Catch Block For Transaction Roll Back 

Here I am going to explain one real life scenario of using TRY-CATCH block. One of the common scenarios is usingTransaction. In a Transaction, we can have multiple operations. If all operations executed successfully, then database will commit otherwise we need to ROLLBACK.

 Collapse | Copy Code

/*
  I want to delete a Particular Records from Both Student
  Details and Library. Database will only commit, iff both 
  delete statement execute successfully, If fails it will Roll
  back. Intentionally  I have passed a wrong roll ( Which causes)
  the exception and transaction will rollback.
*/
BEGIN TRY
   -- Start A Transaction
   BEGIN TRANSACTION   

   -- Delete Student From StudenDetails Table
   DELETE FROM StudentDetails WHERE Roll = '1'
   Print 'Delete Record from Student Details Table'
   -- Delete The Same Student Records From Library Table also
   DELETE FROM Library  WHERE Roll = 'a'
   Print 'Delete Record from Library Table'
   -- Commit if Both Success
   COMMIT
   -- Update Log Details
   Insert into LogDetails(ID,Details) values ('1','Transaction Successful');
END TRY
BEGIN CATCH
 Print 'Transaction Failed - Will Rollback'
  -- Any Error Occurred during Transaction. Rollback
  IF @@TRANCOUNT > 0
    ROLLBACK  -- Roll back
END CATCH

Below is the output:

 Collapse | Copy Code

Delete Record from Student Details Table
Transaction Failed - Will Rollback

Points of Interest

I have written and explained each and every thing very easily and with a practical example. Hope this will help you.

Please give your feedback and suggestions.