Tranasction and TRY – CATCH in SQL SERVER

  BEGIN TRY

      BEGIN TRANSACTION  — BEGIN TRANSACTION should come before or after BEGIN TRY that is not equally important, but it feels more right to have it inside the TRY-CATCH section.

YOUR STATEMENTS COMES HERE

  COMMIT  —   Don’t write this COMMIT  after end of CATCH block because  if you get an error and wind up in the CATCH up section, you will roll back the transaction. When you exit the CATCH block, you will
attempt to commit, but there is nothing to commit, so there will be a new error.

END TRY

BEGIN CATCH

     ROLLBACK;

THROW  — THROWING the original exception to the application. THROW  statement is available in SQL server 2012. RAISEERROR() vs THROW: http://sqlhints.com/2013/06/30/differences-between-raiserror-and-throw-in-sql-server/

END CATCH Continue reading

Using Coalesce() in sqlserver

If you consider the below facts placed in a employee table with Id, Name, ph_no, Alt_no, Office no.

id Name Ph_ no Alt_ no Office no
101 Albert 999999 456453 321333
102 khan null null 123455
103 victor 112121 null null
104 lovely null null 1897321

The above Employee table may have single value or three values. If it has single value, then it fills null values with remaining attributes.

When we retrieve the number from employee table, that number Should Not be Null value. To get not null value from employee table, we use Coalesce() function. It returns the first encountered Not Null Value from employee table.

Continue reading

COALESCE vs. ISNULL SQL SERVER

The COALESCE and ISNULL T-SQL functions are used to return the first nonnull expression among the input arguments. SQL Server practitioners often wonder what the difference is between the two functions. Several differences exist; some are straightforward and are common knowledge, whereas others are less straightforward and are less well-known. Continue reading