SQL - Procedural Programming - TRY ... CATCH

The TRY...CATCH construnct was introduced with SQL Server 2005 for error handling using T-SQL. Statements to be tested for an error are enclosed in a BEGIN TRY ... END TRY block.
A CATCH block immediately follows the TRY block, and error-handling logic is stored here. The following examples shows the basic syntax:

BEGIN TRY
-- code that may produce errors
END TRY
BEGIN CATCH
-- error handling logic
END CATCH

SQL Server evaluates each statement in TRY block sequentially. If a runtime error is encountered, control immediately jumps to the CATCH block, and error information can be retrieved, logged, and displayed to the user.

SQL - Procedural Programming - Variable

A variable is a container for a single data value of a particular type. In Transact-SQL (T-SQL), all variables are preceded by an @ symbol.

Local variables are used in T-SQL scripts for a variety of purposes including:

• Storing values to be tested by control-of-flow statement
• Acting as counter in a loop
• Storing the results of an expression
• Retrieving field values for a single record using a SELECT statement

Variable are also used to pass values into parameters for stored procedures and user-defined functions. When declaring a variable, you must specify its name, datatype and sometimes the length and precision of datatype. The DECLARE statement can be used to declare multiple variables by separating them with commas.

Example

DECLARE @var1 int, @var2 varchar(255);

The preferred way to set the contents of a variable is use the SET statement. It is also possible to use the SELECT statement to set the value of one or more variables.

SET @var1=5;

SELECT @var2='A varchar string';