SQL Server 2012 TSQL enhancements – Part 3


In the previous article SQL Server 2012: TSQL enhancements – Part 2 we have seen Ad-hoc query paging feature of SQL 2012 that can be leverage to implement Paging functionality in query.

Today, we will see few T-SQL features that can be use in most application development.

THROW statement

  • New operator for enhanced error handling.
  • Can be instead of RAISERROR, however RAISERROR requires user defined message to be added to sys.messages table before you use it to raise an error.
  • THROW statement does not require error number to exist in system table.
  • Exception raised by THROW statement always set severity level to 16.
  • The statement before THROW statement need to terminate with semicolon (;).

Syntax-THROW <error number>, <message>, <state>

Code Snippets –

THROW 51000, ‘The record does not exist.’, 1;

GO

DECLARE @TestRethrow TABLE

(    ID INT PRIMARY KEY

);

BEGIN TRY

INSERT @TestRethrow(ID) VALUES(1);

—  Force error 2627, Violation of PRIMARY KEY constraint to be raised.

INSERT @TestRethrow(ID) VALUES(1);

END TRY

BEGIN CATCH

PRINT ‘In catch block.’;

THROW;

END CATCH;

 

 

TRY_CONVERT

  • A new function to handle conversion issues/errors.
  • Returns a value to specified data type in case of success.
  • Return “NULL“ in case of conversion fail.

Syntax- TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

Code Snippets –

SELECT CONVERT(FLOAT, ‘test’) Throws an error – Error converting data type varchar to float.
SELECT TRY_CONVERT(FLOAT, ‘test’) Error is handled by returning NULL

     

IIF

  • Evaluate the Boolean expression and return the True or False value.
  • Can be used instead of CASE statement

Syntax- IIF ( boolean_expression, true_value, false_value )

Code Snippets –

declare @a int =1, @b int = 0

select CASE WHEN @a = @b THEN ‘True’ ELSE ‘False’ END

select IIF(@a = @b, ‘True’, ‘False’)

If you like this post, please share your comments 🙂

Advertisements

One thought on “SQL Server 2012 TSQL enhancements – Part 3

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s