"MSSQL2008 Technology Insider: T-SQL language Basics" Reading notes (bottom)

Index:

I. Architecture of SQL Server

Second, query

Third, table expression

Four, set operation

V. Perspective, inverse perspective and grouping

Vi. Modification of data

VII. Transactions and concurrency

Eight, programmable objects

V. Perspective, inverse perspective and Grouping 5.1 perspectives

The so-called perspective (pivoting) is the process of rotating data from the state of a row into a column state. Its processing steps are:

I believe many people in the written test or interview when asked how to achieve row-to-column or column-changing problems, many people may have been confused, it's okay, let's take the example below to understand.

(1) Prepare the data

View Code

This uses the MS SQL2008 's VALUES clause format syntax, which is the new feature of version 2008. If you are using versions 2005 and below, you need multiple INSERT statements. The final execution results are as follows:

(2) Requirements description

Suppose we want to generate a report that contains the total order quantity between each employee and the customer combination. This problem can be solved by using the following simple grouping query:

Select Empid,custid,sum (qty) as Sumqty from dbo. Ordersgroup by Empid,custid;

The results of this query are as follows:

　　

However, suppose you now require the output to be generated in the format shown in the following table:

　　

At this point, we need to have a perspective transformation!

(3) Pivot conversion using standard SQL

Step1. Group: GROUP by Empid;

Step2. Extension: Case when custid= ' A ' and then qty END;

Step3. Aggregation: SUM (case if custid= ' A ' then qty END);

--1.1 Standard SQL Pivot conversion select Empid,    sum (case is custid= ' a ' then Qty End) as A,    sum (case is custid= ' B ' then qty end) AS B,    sum (case is custid= ' C ' then qty End) as C,    sum (case is custid= ' D ' then qty end) as Dfrom dbo. Ordersgroup by Empid;

The execution results are as follows:

　　

(4) using the T-SQL pivot operator for perspective conversion

Since SQL Server 2005 has introduced a T-SQL Unique Table operator-pivot, which can manipulate a source table or table expression, pivot the data, and return a result table.

The pivot operator also involves the three logical processing stages described earlier (grouping, extending, and aggregating) and the same perspective transformation elements, but using differentSQL Server native Syntax .

The following is the same effect that is achieved with the pivot operator:

Select Empid,a,b,c,dfrom (select Empid,custid,qty from      dbo. Orders) as D  pivot (SUM (qty) for CustID in (a,b,c,d)) as P;

Within the parentheses of the pivot operator, you specify the aggregate function (sum in this example), the aggregation element (qty in this example), the extension element (CustID), and the list of destination column names (in this case, a, B, C, D). After the parentheses of the pivot operator, you can create an alias for the result table.

Tip: instead of applying it directly to the source table (the Orders table in this example), use the pivot operator to apply it to a table expression that contains only the 3 elements required by the perspective transformation and does not contain other properties. In addition, you do not need to explicitly specify the grouping element for it, and you do not need to use the GROUP BY clause in the query.

5.2 Inverse Perspective

The so-called inverse perspective (unpivoting) transformation is a technique that rotates data from the state of a column to the state of a row, extending the value of multiple columns from a single record to a single column that has the same worth of multiple records. In other words, each source row in the pivot table is potentially converted to more than one row, and each row represents a specified column value for the source pivot table.

or through a chestnut to understand:

(1) First, prepare the data:

View Code

The following is a query result for this table empcustorders:

　　

(2) Requirements description

Requires that you perform a perspective transformation that returns a row of records for each employee and customer combination that contains the order quantity for this combination. The expected output results are as follows:

　　

(3) Reverse perspective conversion for standard SQL

Step1. Generating replicas: Cross join crosses join to generate multiple replicas

Step2. Extracting elements: Generating Qty Data columns from Case statements

Step3. Deleting unrelated intersections: filtering out null values

Select *from (select Empid, CustID, Case CustID if '            a ' then a while ' B ' then B is '            C ' then C when            ' d ' then D        -end as qty from      dbo. Empcustorders Cross        Join (VALUES (' A '), (' B '), (' C '), (' D ')) as Custs (CustID)) as Dwhere qty are not null;

The execution results are as follows:

　 　

(4) T-SQL UNPIVOT operator for inverse perspective conversion

Similar to pivot, a UNPIVOT operator is introduced in SQL Server 2005, which works just as opposed to the pivot operator, which is what we can do with inverse perspective transformations. Unpivot also goes through the three stages we mentioned above. To continue with the chestnuts above, we use Unpivot for inverse perspective transformations:

Select Empid, CustID, Qtyfrom dbo. Empcustorders  Unpivot (qty for CustID in (a,b,c,d)) as U;

The parentheses behind the UNPIVOT operator include the target column that holds the value of the Source table column (This is qty), the target column name for the source table column name (here is CustID), and the list of source table column names (A, B, C, D). Similarly, you can follow an alias after the Unpivot parenthesis.

Tip: You can't get the original table by inverting the pivot transformation of the table. Because your perspective transformation simply rotates the value of the pivot transformation into another new format of the island.

5.3 Grouping

First look at the grouping set: The grouping set is the set of attributes (or column names) used by the grouping (GROUP BY clause). In traditional SQL, an aggregate query can define only one grouping set. To handle grouping sets flexibly and efficiently, SQL Server 2008 introduces several important new features ( they are all subordinate clauses of group by and need to rely on the GROUP BY clause ):

(1) GROUPING sets subordinate clauses

This clause makes it easy to define multiple grouping sets in the same query. For example, we define 4 sets of groupings: (Empid,custid), (Empid), (CustID) and ():

--3.1grouping sets subordinate clause select Empid,custid,sum (qty) as Sumqtyfrom dbo. Ordersgroup by   GROUPING sets  (    (Empid,custid),    (Empid), (    CustID), (    )   );

This query is equivalent to executing the set of four group by queries.

(2) cube dependent clauses

The CUBE clause provides a more abbreviated way to define multiple grouping sets, and you can think of the cube clause as the power set used to generate the grouping. For example: CUBE (a,b,c) is equivalent to grouping sets[(A,B,C), (A, B), (A,c), (B,c), (a), (b), (c), ()]. Here we use cube to implement the above example:

--3.2cuee subordinate clauses Select Empid,custid,sum (qty) as Sumqtyfrom dbo. Ordersgroup by Cube (Empid,custid);

(3) Rollup subordinate clauses

The ROLLUP clause is also a shorthand method, except that it differs from cube, which emphasizes that there is a certain hierarchical relationship between the input members, thus generating all the grouping sets that make sense for this hierarchical relationship. For example, Cube (A,B,C) generates 8 possible grouping sets, whereas rollup considers that there is a a>b>c hierarchy of 3 input members, so only 4 grouping sets are generated: (A,b,c), (A, B), (a), ().

Let's pretend that we want to have a time-level relationship: Order year > Order month > Order date, define all grouping sets in such a relationship, and not return the total order quantity for each grouping set. Maybe we need 4 rows with grouping sets, and then we use rollup only one line: GROUP byRollup(Year (OrderDate), MONTH (OrderDate), Day (OrderDate)) ;

The full SQL query is as follows:

--3.3rollup slave clause select year  (OrderDate) as OrderYear,  MONTH (OrderDate) as OrderMonth, Day  (OrderDate) as Orderday,  SUM (qty) as Sumqtyfrom dbo. Ordersgroup by Rollup (year (OrderDate), MONTH (OrderDate), Day (OrderDate));

The execution results are as follows:

(4) grouping_id function

If a query defines multiple grouping sets, it also wants to associate the result row with the grouping set, that is, to label each result row with which grouping set it is associated. SQL Server 2008 introduces a grouping_id function that simplifies the processing of associated result rows and grouping sets, and makes it easy to calculate the association of each row and which grouping set.

For example, to continue with the example above, we want to use Empid,custid as input:

View Code

In the execution result, Groupingset will appear as 0,1,2,3, representing the 4 possible groupings of Empid,custid ((Empid,custid), (Empid), (CustID), (()), respectively.

VI. Data modification 6.1 inserting and deleting data 6.1.1 look at my fancy inserting data

①insert Values statement: I'm afraid we're more familiar with this statement. This statement can be seen on any of the books in the database.

INSERT into dbo. Orders (OrderID, OrderDate, Empid, CustID)  VALUES (10001, ' 20090212 ', 3, ' A ');

It is necessary to understand that, as mentioned earlier, SQL Server 2008 enhances the functionality of the values statement, allowing you to specify a comma-separated multiline record in a single statement. For example, the following statement inserts 4 rows of data into orders:

View Code

②insert SELECT statement: Inserts a set of result rows returned by the select query into the target table.

INSERT into dbo. Orders (OrderID, OrderDate, Empid, CustID)  SELECT OrderID, OrderDate, Empid, CustID  from TSQLFundamentals2008.Sales.Orders  WHERE shipcountry = ' UK ';

③insert EXEC statement: Inserts the result set returned by the stored procedure or dynamic SQL batch into the target table.

The following example shows how to execute a stored procedure usp_getorders and insert the results into the Orders table:

INSERT into dbo. Orders (OrderID, OrderDate, Empid, CustID)  EXEC TSQLFundamentals2008.Sales.usp_getorders @country = ' France ';

④select into statement: It creates a target table and populates it with the results returned by the query. It is important to note that it is not a standard SQL statement (that is, not part of the ANSI SQL standard) and cannot be used to insert data into a table that already exists .

--Ensure that the target table does not exist if object_id (' dbo '. Orders ', ' U ') are not NULL DROP TABLE dbo. Orders; SELECT OrderID, OrderDate, Empid, Custidinto dbo. Ordersfrom TSQLFundamentals2008.Sales.Orders;

⑤bulk INSERT statement: Used to import data from a file into an existing table, you need to develop a target table, a source file, and some other options.

The chestnut below demonstrates how to insert the data capacity in the file "C:\testdata\orders.txt" (BULK Insert) into the Orders table, specifying the file type as character format, the field terminator as a comma, and the line terminator as the newline character (\ t):

BULK INSERT dbo. Orders from ' C:\testdata\orders.txt '  with     (       datafiletype    = ' char ',       fieldterminator = ', ',       Rowterminator   = ' \ n '    );

6.1.2 Look at my fancy delete data

①delete statement: Standard SQL statement, the most common usage for everyone.

DELETE from dbo. Orderswhere OrderDate < ' 20070101 ';

②truncate statement: Not a standard SQL statement that never deletes all rows in a table, and does not require a filter condition.

Tip: Truncate and delete are significantly different in performance, and for a table with millions of rows of records, truncate can be resolved in a matter of seconds, and delete may take several minutes. Because Truncate records the log in the smallest mode, delete logs the log in full mode. So, gentlemen, use truncate with caution. Therefore, we can create a virtual table (Dummy table) that contains a foreign key to the product table so that the product table can be protected.

③ Join-based Delete: Also not a standard SQL statement, you can delete rows of data in a table based on a filter defined by the properties of the related rows in another table.

For example, the following statement is used to delete an order from a U.S. customer:

DELETE from Ofrom dbo. Orders as O  JOIN dbo. Customers as C on    O.custid = c.custidwhere c.country = N ' USA ';

Of course, if you want to use standard SQL statements, you can do the following:

DELETE from dbo. Orderswhere EXISTS  (SELECT * from   dbo. Customers as C   WHERE Orders.custid = C.custid and     c.country = N ' USA ');

6.2 Updating and merging data 6.2.1 Fancy Update data

①update statement: No explanation, everybody's using it.

Here's a look at two different chestnuts, the first of which is about the nature of simultaneous operation. Look at the following UPDATE statement:

UPDATE dbo. T1  SET col1 = col1 +, col2 = col1 + 10;

Assume that the col1 column in the T1 table is listed as 100,col2 200. How much is it after the calculation?

The answer is announced: col=110,col=110.

Then look at a chestnut, suppose we want to achieve two number of exchanges how to do? We may be impatient to say temporary variables. However, all assignment expressions in SQL seem to be computed at the same time, and there is no need for temporary variables to solve this problem.

UPDATE dbo. T1  SET col1 = col2, col2 = col1;

② Join-based UPDATE statement: The same is not the SQL standard syntax, where joins are filtered as a join-based delete.

UPDATE OD  SET discount = discount + 0.05FROM dbo. OrderDetails as OD  JOIN dbo. Orders as O on    od.orderid = O.orderidwhere CustID = 1;

Similarly, if you want to use standard SQL syntax, you can use subqueries instead of joins:

UPDATE dbo. OrderDetails  SET Discount = discount + 0.05WHERE EXISTS  (SELECT * FROM dbo. Orders as O   WHERE O.orderid = Orderdetails.orderid and     CustID = 1);

③ Assignment Update: This is a T-SQL-specific syntax that allows you to update the data in a table and assign a value to a variable. You do not need to use separate update and SELECT statements to accomplish the same task.

Suppose we have a table sequence, which has only one column Val, all ordinal numbers. We can get a new sequence value by assigning a value update:

DECLARE @nextval as INT; UPDATE Sequence SET @nextval = val = val + 1; SELECT @nextval;

6.2.2 New gameplay: Merging data

SQL Server 2008 introduces a statement called Merge that can make different modifications to the data (Insert/update/delete) in a single statement based on logical conditions. The merge statement is part of the SQL Standard, and the T-SQL version of the merge statement adds some non-standard extensions.

Let's look at how to merge, first we prepare two tables customers and Customersstage:

View Code

The execution results are as follows:

　　

Now we want to add customers that don't exist and update existing customers. SOURCE table: Customersstage, target table: Customers.

MERGE into dbo. Customers as tgtusing dbo. Customersstage as SRC on  Tgt.custid = Src.custidwhen matched then  UPDATE SET    tgt.companyname = Src.companyname,    tgt.phone = Src.phone,    tgt.address = Src.addresswhen not matched then   INSERT (CustID, CompanyName, phone, address)  VALUES (Src.custid, Src.companyname, Src.phone, src.address);

Predicate condition: Tgt.custid=src.custid is used to define what data is matched and what data does not match.

Tips: Themerge statement must end with a semicolon , and is optional for most of the other statements in T-SQL. However, it is recommended to follow best practices, ending with semicolons.

6.3 Advanced Data Update methods

① modifying data through table expressions

--updateupdate OD  SET discount = discount + 0.05FROM dbo based on the join. OrderDetails as OD  JOIN dbo. Orders as O    on od.orderid = O.orderidwhere CustID = 1;--based on the table expression (here is the CTE) Updatewith C as (  SELECT CustID, Od.orderid ,    ProductID, discount, discount + 0.05 as Newdiscount from  dbo. OrderDetails as OD    JOIN dbo. Orders as O on      od.orderid = O.orderid  WHERE custid = 1) UPDATE C  SET discount = newdiscount;

② data updates with the top option

--Deletes the first 50 rows of delete top from the dbo. The orders;--updates the first 50 rows of update top (dbo). Orders  SET freight = freight + 10.00;--based on the CTE deletes the first 50 rows with the C as (  SELECT TOP () from  dbo. Orders  ORDER by OrderID) The DELETE from c;--is based on the first 50 rows of the CTE update with the C as (  SELECT TOP () from  dbo. Orders  ORDER by OrderID DESC) UPDATE C  SET freight = freight + 10.00;

6.4 OUTPUT clause

In some scenarios where we want to be able to return data from modified rows, we can use the OUTPUT clause. SQL Server 2005 introduces an OUTPUT clause that enables the ability to return data from a modified statement by adding an output clause to the modification statement.

① INSERT statement with output

INSERT into dbo. T1 (datacol)  OUTPUT inserted.keycol, Inserted.datacol    SELECT lastname    from TSQLFundamentals2008.HR.Employees    WHERE country = N ' USA ';

② DELETE statement with output

DELETE from dbo. Orders  OUTPUT    deleted.orderid,    deleted.orderdate,    deleted.empid,    deleted.custidwhere OrderDate < ' 20080101 ';

③ UPDATE statement with output

UPDATE dbo. OrderDetails  SET Discount = discount + 0.05OUTPUT  Inserted.productid,  Deleted.discount as Olddiscount,  Inserted.discount as Newdiscountwhere ProductID = 51;

④ the merge statement with output

MERGE into dbo. Customers as tgtusing dbo. Customersstage as SRC on  Tgt.custid = Src.custidwhen matched then  UPDATE SET    tgt.companyname = Src.companyname,    tgt.phone = Src.phone,    tgt.address = Src.addresswhen not matched then   INSERT (CustID, CompanyName, phone, address)  VALUES (Src.custid, Src.companyname, Src.phone, src.address) OUTPUT $action, Inserted.custid,  deleted.companyname as Oldcompanyname,  inserted.companyname as Newcompanyname,  Deleted.phone as Oldphone,  Inserted.phone as Newphone,  deleted.address as Oldaddress,  Inserted.address as newaddress;

The above merge statement uses the OUTPUT clause to return the values of the old and new versions of the modified rows. There is no old version of the value for the insert operation, so the values for all deleted columns return NULL. The $action function tells us whether the lost trip is an update or an insert operation.

　　

VII. Transactions and concurrency

Considering the content of this chapter is more and very important, the book inside the chestnut is not many, intends to refer to a number of information and then separate it to write an independent article, to the share to you, please understand.

Viii. Programmable Object 8.1 variables and batch processing

(1) Variable: declare+set/select

The DECLARE statement can declare one or more variables, and then use the Set/select statement to set a variable to the specified value.

①set statements can only operate on one variable at a time

--set mode declare @i as Intset @i=10;--sql Server 2008 can declare and initialize variables declare @i as int = 10 in the same statement;

The ②select statement allows multiple values obtained from the same row to be assigned to multiple variables.

--select mode declare @firstname as nvarchar, @lastname as nvarchar (+), select  @firstname = firstname,  @ LastName = Lastnamefrom hr. Employeeswhere Empid=3;select @firstname as FirstName, @lastname as LastName;

A SET statement is more secure than copying a SELECT statement because it requires that the data be extracted from the table using a scalar subquery. If, at run time, the scalar subquery returns multiple values, the query fails. For example, the following code will error at run time:

The--set is more secure than the SELECT statement @empname @empname as nvarchar (declare), Set, = (SELECT FirstName + N ' + LastName from                HR. Employees                where mgrid=2);                Select @empname as EmpName;

　　

(2) Batch processing: a set of single or multiple T-SQL statements that a client application sends to SQL Server, and SQL Server takes a batch statement as an individual executable unit.

The following is an example of a batch, but note that if there is a syntax error in the batch, the entire batch is not committed to SQL Server.

--a batch as a Unit of parsing--Valid batchprint ' first Batch '; Use TSQLFundamentals2008; go--Invalid batchprint ' Second batch '; SELECT CustID from Sales.customers; SELECT OrderID FOM sales.orders; --This sentence has a syntax error, so the entire batch cannot be submitted to SQL Server execution go--Valid batchprint ' third batch '; SELECT Empid from HR. Employees; GO

Tip: Unlike batching and transactions, a transaction is a working atomic unit of work, and a batch can contain multiple transactions, and a transaction can also be committed in some parts of multiple batches. When a transaction is canceled or rolled back in execution, SQL Server revokes some of the activity since the start of the transaction, regardless of where the batch started.

8.2 Process Control

(1) IF ... ELSE

This should all be known, but it is important to note that T-SQL uses three-valued logic, and you can activate the Else statement block when the condition evaluates to False or unknown. If a condition value may be false or unknown (for example, a null value is involved), and a different processing is required for each case, a null value must be explicitly tested with the IS NULL predicate.

The following If-else code demonstrates: If today is the first day of one months, a full backup of the database; If today is the last day of one months, a differential backup of the database (the so-called differential backup means only the updates that have been made since the last full backup).

IF Day (current_timestamp) = 1BEGIN PRINT ' Today's the first day of the  month.  PRINT ' Starting a full database backup. ';  BACKUP DATABASE TSQLFundamentals2008 to    DISK = ' C:\Temp\TSQLFundamentals2008_Full.BAK ' with INIT;  PRINT ' finished full database backup '; Endelsebegin PRINT ' Today is isn't the first day of the  month. '  PRINT ' Starting a differential database backup. ';  BACKUP DATABASE TSQLFundamentals2008 to    DISK = ' C:\Temp\TSQLFundamentals2008_Diff.BAK ' with INIT;  PRINT ' finished differential database backup. '; Endgo

This assumes that the backup file path directory c:temp already exists.

(2) While: Do not explain, you should all understand.

DECLARE @i as INT; SET @i = 1; While @i <= 10BEGIN  PRINT @i;  SET @i = @i + 1; END; GO

8.3 Cursors

A type of object called a cursor is supported in T-SQL, which can be used to process rows in the result set returned by the query, processing only one row at a time in the specified order . This is handled in contrast to the use of set-based queries, where the common query is to handle the collection as a whole without any order.

In other words, using a cursor, like fishing with a fishing rod, can only be hooked to a fish at a time. The use of collections, like fishing in nets, one can catch a whole net fish. Therefore, we should take a lot of consideration to the scene using cursors. In general, it is more efficient to use cursors if you are dealing with a row of cursors in a fixed order that involves much less data access than a collection-based approach, as is the case with successive aggregations mentioned in the previous article.

How do I use cursors?

Here's a look at an example that uses cursors to calculate the total number of consecutive orders per customer for each month in the CustOrders view (continuous aggregation case):

--example:running aggregationsset NOCOUNT on;  Use Tsqlfundamentals2008;declare @Result TABLE (custid int, ordermonth DATETIME, qty int, Runqty int,  PRIMARY KEY (CustID, OrderMonth));D eclare @custid as int, @prvcustid as int, @ordermonth DATETIME, @qty as  INT, @runqty as Int;declare C CURSOR fast_forward/* Read Only, FORWARD only */For SELECT CustID, OrderMonth, Qty From Sales.custorders ORDER by CustID, Ordermonth;open Cfetch NEXT from-C into @custid, @ordermonth, @qty; SELECT @prvcustid = @custid, @runqty = 0;  While @ @FETCH_STATUS = 0BEGIN IF @custid <> @prvcustid SELECT @prvcustid = @custid, @runqty = 0;  SET @runqty = @runqty + @qty;    INSERT into @Result VALUES (@custid, @ordermonth, @qty, @runqty); FETCH NEXT from C into @custid, @ordermonth, @qty; Endclose c;deallocate C; SELECT CustID, CONVERT (VARCHAR (7), OrderMonth, 121) as OrderMonth, qty, Runqtyfrom @ResultORDER by CustID, OrderMonth ; GO 

The execution results are as follows:

　　

8.4 Temporary tables

Sometimes it is necessary to temporarily save the data to a table, and in some cases we may not want to use a permanent table. In this case, it may be more convenient to use a temporary table.

(1) Local temp table: Only the session that created it is visible at the creation level and at the internal level (internal procedures, functions, triggers, and so on) that the call is made to, and it is automatically deleted when the creation session is disconnected from the SQL Server instance.

To create a temporary partial table, simply prefix the name with a single # sign:

View Code

(2) Global temp table: can be visible to all other sessions, when the session that created the staging table disconnects the database, and there is no activity when referencing the global temporary table, SQL Server automatically deletes the corresponding global temporary table.

To create a global local table, you only need to prefix the name with two # numbers:

View Code8.5 Dynamic SQL

SQL Server allows the use of strings to dynamically construct a batch of T-SQL code, and then execute the batch, a feature called Dynamic SQL (Daynamic sql).

(1) using the EXEC (execute abbreviation) command

View Code

(2) using sp_executesql stored procedures

The sp_executesql stored procedure has two input parameters and one parameter assignment part: the first parameter needs to specify the Unicode string containing the batch code you want to run, and the second parameter is a Unicode string that contains the life of all the input and output parameters in the first parameter. Then specify values for the input and output parameters, separated by commas.

View Code

Tips:

The ①sp_executesql stored procedure performs better than exec because its parameterization facilitates the reuse of cached execution plans.

②sp_executesql stored procedures are also more secure than Exec, and its parameterization is not subject to SQL injection.

8.6 Routines: User-defined functions, stored procedures, and triggers

(1) User-defined function: encapsulates the logical processing of the calculation, it may be necessary to base on the input parameters and return the results.

The following example creates a user-defined function, Dbo.fn_age, for a given birth date and event date, that function can return an individual's age at the time date:

View Code

(2) Stored procedure: encapsulates a T-SQL code to a server-side routine that can have input and output parameters that can return a result set of multiple queries.

The following example creates a stored procedure, usp_getcustomerorders, that takes a customer ID and a date range as input parameters, returns a result set of orders in the Orders table that are placed by the specified customer within the specified date range, and also takes the behavior affected by the query as an output parameter.

View Code

Tips: Stored procedures can encapsulate business logic processing, better control security (to help avoid SQL injection), and improve execution performance (reduce network traffic).

(3) Trigger: A special stored procedure that, whenever a particular event occurs, invokes the trigger and runs its code. SQL Server supports two types of related triggers: DML triggers and DDL triggers.

The following example shows a simple DML trigger that audits the data that is inserted into the table (inserted into the Audit audit table).

View Code8.7 Error Handling

The T-SQL code provides a way to become a try ... The structure of the catch, introduced in SQL Server 2005.

BEGIN TRY  PRINT 10/2;  PRINT ' No error '; END trybegin CATCH  PRINT ' Error '; END Catchgo

For error-handling code, in real-world development, you can encapsulate the creation of a stored procedure to reuse the error code

"MSSQL2008 Technology Insider: T-SQL language Basics" Reading notes (bottom)