Sql server® 2012 t-sql fundamentals

 

CHAPTER 9

 

Transactions and Concurrency

 

 337

3-6

Can you suggest a way to prevent this deadlock? Hint: Refer back to what you read in the “Deadlocks” 

section.

3-7

Run the following code for cleanup.

UPDATE Production.Products 

  SET unitprice = 19.00 

WHERE productid = 2; 

 

UPDATE Production.Products 

  SET unitprice = 10.00 

WHERE productid = 3;

www.it-ebooks.info

---

www.it-ebooks.info

---

  

339

C H A P T E R   1 0

programmable Objects

T

his chapter provides a brief overview of programmable objects to familiarize you with the capa-

bilities of Microsoft SQL Server in this area and with the concepts involved. The chapter covers 

variables; batches; flow elements; cursors; temporary tables; routines such as user-defined functions, 

stored procedures, and triggers; and dynamic SQL. The purpose of this chapter is to provide a high-

level overview, not to delve into technical details. Try to focus on the logical aspects and capabilities of 

programmable objects rather than trying to understand all code elements and their technicalities. 

Variables

Variables allow you to temporarily store data values for later use in the same batch in which they were 

declared. I describe batches later in this chapter, but for now, the important thing for you to know is 

that a batch is one T-SQL statement or more sent to SQL Server for execution as a single unit. 

Use a DECLARE statement to declare one or more variables, and use a SET statement to assign a 

value to a single variable. For example, the following code declares a variable called @i of an INT data 

type and assigns it the value 10.

DECLARE @i AS INT; 

SET @i = 10;

SQL Server 2008 and SQL Server 2012 support the declaration and initialization of variables in the 

same statement, like this.

DECLARE @i AS INT = 10;

When you are assigning a value to a scalar variable, the value must be the result of a scalar expres-

sion. The expression can be a scalar subquery. For example, the following code declares a variable 

called @empname and assigns it the result of a scalar subquery that returns the full name of the 

employee with an ID of 3.

USE TSQL2012; 

 

DECLARE @empname AS NVARCHAR(31); 

 

SET @empname = (SELECT firstname + N' ' + lastname 

                FROM HR.Employees 

                WHERE empid = 3); 

 

SELECT @empname AS empname;

www.it-ebooks.info

---

340  

Microsoft SQL Server 2012 T-SQL Fundamentals

This code returns the following output.

empname 

---------- 

Judy Lew

The SET statement can operate only on one variable at a time, so if you need to assign values to 

multiple attributes, you need to use multiple SET statements. This can involve unnecessary overhead 

when you need to pull multiple attribute values from the same row. For example, the following code 

uses two separate SET statements to pull both the first and the last names of the employee with the 

ID of 3 to two separate variables.

DECLARE @firstname AS NVARCHAR(10), @lastname AS NVARCHAR(20); 

 

SET @firstname = (SELECT firstname 

                  FROM HR.Employees 

                  WHERE empid = 3); 

SET @lastname = (SELECT lastname 

                 FROM HR.Employees 

                 WHERE empid = 3); 

 

SELECT @firstname AS firstname, @lastname AS lastname;

This code returns the following output.

firstname  lastname 

---------- --------- 

Judy       Lew

SQL Server also supports a nonstandard assignment SELECT statement, which allows you to query 

data and assign multiple values obtained from the same row to multiple variables by using a single 

statement. Here’s an example.

DECLARE @firstname AS NVARCHAR(10), @lastname AS NVARCHAR(20); 

 

SELECT 

  @firstname = firstname, 

  @lastname  = lastname 

FROM HR.Employees 

WHERE empid = 3; 

 

SELECT @firstname AS firstname, @lastname AS lastname;

The assignment SELECT has predictable behavior when exactly one row qualifies. However, note 

that if the query has more than one qualifying row, the code doesn’t fail. The assignments take place 

per each qualifying row, and with each row accessed, the values from the current row overwrite the 

existing values in the variables. When the assignment SELECT finishes, the values in the variables are 

those from the last row that SQL Server happened to access. For example, the following assignment 

SELECT has two qualifying rows.

www.it-ebooks.info

---