T-SQL tricks for SQL Server 2012: T-SQL statements

Editor's note: This is the fourth and final part of a series on new features in T-SQL in SQL Server 2012. Check out the other parts of this series to learn more about the joy of data values

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at [email protected].

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in August 2012

, T-SQL functions and T-SQL formatting.

There might be times when you want to access data from a previous row in a result set or from a row that follows, without having to create a self-join. That's where the LAG and LEAD functions come in. The LAG function lets you retrieve data from a previous row, and the LEAD function lets you pull data from a row that follows. You can then compare that data to values in the current row.

LAG and LEAD

Let's look at an example to demonstrate how this works. The following SELECT statement retrieves data from the SalesOrderHeader table and groups the information based on year:

SELECT YEAR(OrderDate) AS OrderYear,

  SUM(SubTotal) AS Subtotal,

  LAG(SUM(SubTotal), 1, 0) OVER (ORDER BY YEAR(OrderDate)) AS LastYear,

  LEAD(SUM(SubTotal), 1, 0) OVER (ORDER BY YEAR(OrderDate)) AS NextYear

FROM Sales.SalesOrderHeader

GROUP BY YEAR(OrderDate)

ORDER BY YEAR(OrderDate);

For each year, the T-SQL statements retrieve the total of Subtotal values, plus the totals from the previous year and the year to follow. To retrieve the totals from the previous year, the statement uses the LAG function in the third column expression. The function takes three arguments. The first is an expression that returns a scalar value, in this case, a total of the SubTotal values. The second argument specifies the number of rows to go back. Because 1 is used, the data will be taken from the previous row. The final argument specifies the value to return if the preceding row returns a NULL. In this case, 0 will be used.

For more on T-SQL statements

T-SQL statements in subqueries- the basics

T-SQL statements and performance issues

The LAG function also requires an OVER clause to determine the order of the data before the function is applied. This ensures that you pull your value from the correct row.

To retrieve data from the row that follows the current row, the SELECT statement uses the LEAD function, which works just like the LAG function except it moves forward in the result set, rather than backward. Because these two functions are used, the SELECT statement returns the results shown in the following table:

Each row includes the total sales for the current year, the previous year, and the year to follow. For example, in the first row, the LastYear value is 0.00. Because there are no previous years, the total for that year is NULL. As a result, the LAG function converts the NULL to 0.00. The same holds true for the last row. Because it represents the last year for which results are available, the NextYear value is 0.00, which was converted from NULL.

RESULTS SETS

Before SQL Server 2012, the EXECUTE statement's WITH clause supported only the RECOMPILE option. However, the clause now also supports the RESULTS SETS option, which helps you better control the result sets a stored procedure returns. One of the things you can do with that option is to redefine the result set definition, without having to change the stored procedure itself.

For example, the AdventureWorks2012 database includes the stored procedure uspGetEmployeeManagers. The following table shows the columns and their data types that procedure returned. [The Name data type is a user-defined type based on the NVARCHAR(50) built-in data type.]

By using the RESULT SETS option, you can change the column name, data type (if an implicit conversion is supported), collation, and nullability. For example, suppose you want to shorten the column names the stored procedure returned and change a couple data types. You can include the RESULT SETS option when you run the EXECUTE statement, as shown in the following example:

EXECUTE uspGetEmployeeManagers 100

WITH RESULT SETS

(

  (

    RecursLevel INT,

    EmplID INT,

    FName NVARCHAR(50),

    LName NVARCHAR(50),

    OrgNode HIERARCHYID,

    MgrFName NVARCHAR(50),

    MgrLName NVARCHAR(50)

  )

);

 

When you include a result set definition in your WITH clause, you must include both the column names and data types. The collation and nullability are optional. In this case, I've changed the columns names and several of the data types. The following table shows the results returned by the stored procedure.

As you can see, the column names are now different from those defined in the stored procedure. (The change in data types has no visible effect on the result set.) You could have just as easily provided a different set of names and data types. But again, those data types must support implicit conversions from the original types.

THROW

In the past, when you wanted to return an error message in a TRY…CATCH construction, you would use RAISEERROR to generate that message, but Transact-SQL now supports the THROW statement, which you can use in the CATCH block to re-throw an exception while preserving the original message. To do so, you simply specify the THROW keyword without any parameters. The following example demonstrates how to include a THROW statement in your CATCH block:

BEGIN TRY

  SELECT CONVERT(INT, 'books');

END TRY

BEGIN CATCH

  THROW;

END CATCH;

 

The THROW statement re-throws the original error caught by the CATCH block so the error can be handled at the application level. In the example above, the SELECT statement fails because the string value cannot be converted. As a result, the THROW statement returns the following message:

Msg 245, Level 16, State 1, Line 2

Conversion failed when converting the varchar value 'books' to data type int.

 

The THROW statement also lets you specify parameters if you want to return a custom message. To do so, provide a message number, the message text and a state to associate with the message. You must use a message number outside of those with predefined messages attached to them. You can use any numerical value between 50000 and 2147483647. The following CATCH block includes a THROW statement that defines a custom message:

BEGIN TRY

  DECLARE @string VARCHAR(50) = 'books'

  DECLARE @message VARCHAR(100) =

    CONCAT('You cannot convert "',  @string, '" to the INT data type.')

  SELECT CONVERT(INT, @string);

END TRY

BEGIN CATCH

  THROW 55000, @message, 1;

END CATCH

 

The first parameter in the THROW statement is the value 55000. This is the message number, which falls within the acceptable range. The second parameter is the message itself, in this case, is passed to the statement via the @message variable. The third parameter is the value 1, which indicates the state to associate with the message. You can specify between 0 and 255. The THROW statement returns the following message:

Msg 55000, Level 16, State 1, Line 8

You cannot convert "books" to the INT data type.

 

As you can see, the results include the message and state numbers, plus the level and line numbers. In this case, that line number corresponds to the THROW statement, not the statement that actually produced the error. However, if you refer back to the preceding example, you'll see that the results accurately reflect the error line. This is because when you use the THROW statement without parameters, it merely re-throws the original error.

MOVING AHEAD WITH T-SQL

As you can see, SQL Server 2012 introduces many enhancements to Transact-SQL. You'll also find new system stored procedures and dynamic management views, along with plenty of other updates. Of course, there's a flip side to all these changes -- the deprecated features that will not be supported in future SQL Server versions. You should be just as aware of them as you are of anything new. But for those interested specifically in the latest Transact-SQL changes, the language elements described in these articles should provide a good overview of the many enhancements that are available now and should be available in the foreseeable future.

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top