Sql server® 2012 t-sql fundamentals

 

CHAPTER 10

 

Programmable Objects

 

 341

DECLARE @empname AS NVARCHAR(31); 

 

SELECT @empname = firstname + N' ' + lastname 

FROM HR.Employees 

WHERE mgrid = 2; 

 

SELECT @empname AS empname;

The employee information that ends up in the variable after the assignment SELECT finishes de-

pends on the order in which SQL Server happens to access those rows—and you have no control over 

this order. When I ran this code I got the following output.

empname 

---------- 

Sven Buck

The SET statement is safer than assignment SELECT because it requires you to use a scalar subque-

ry to pull data from a table. Remember that a scalar subquery fails at run time if it returns more than 

one value. For example, the following code fails.

DECLARE @empname AS NVARCHAR(31); 

 

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

                FROM HR.Employees 

                WHERE mgrid = 2); 

 

SELECT @empname AS empname;

Because the variable was not assigned a value, it remains NULL, which is the default for variables 

that were not initialized. This code returns the following output. 

Msg 512, Level 16, State 1, Line 3 

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, 

<= , >, >= or when the subquery is used as an expression. 

empname 

-------- 

NULL

Batches

A batch is one or more T-SQL statements sent by a client application to SQL Server for execution as 

a single unit. The batch undergoes parsing (syntax checking), resolution (checking the existence of 

referenced objects and columns), permissions checking, and optimization as a unit. 

Don’t confuse transactions and batches. A transaction is an atomic unit of work. A batch can have 

multiple transactions, and a transaction can be submitted in parts as multiple batches. When a trans-

action is canceled or rolled back in midstream, SQL Server undoes the partial activity that has taken 

place since the beginning of the transaction, regardless of where the batch began. 

www.it-ebooks.info

---

342  

Microsoft SQL Server 2012 T-SQL Fundamentals

Client application programming interfaces (APIs) such as ADO.NET provide you with methods for 

submitting a batch of code to SQL Server for execution. SQL Server utilities such as SQL Server Man-

agement Studio, SQLCMD, and OSQL provide a client command called GO that signals the end of a 

batch. Note that the GO command is a client command and not a T-SQL server command.