Sql server® 2012 t-sql fundamentals

an example of Using 

IF

 and 

WHILE

The following example illustrates how you can combine the use of the IF and WHILE elements. The 

purpose of the code in this example is to create a table called dbo.Numbers and populate it with 

1,000 rows with the values 1 through 1,000 in the column n.

SET NOCOUNT ON; 

IF OBJECT_ID('dbo.Numbers', 'U') IS NOT NULL DROP TABLE dbo.Numbers; 

CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY); 

GO 

 

DECLARE @i AS INT = 1; 

WHILE @i <= 1000 

BEGIN 

  INSERT INTO dbo.Numbers(n) VALUES(@i); 

  SET @i = @i + 1; 

END

The code uses the IF statement to check whether the Numbers table already exists in the current 

database, and if it does, the code drops it. The code then uses a WHILE loop to iterate 1,000 times 

and populate the Numbers table with the values 1 through 1,000.

Cursors

In Chapter 2, “Single-Table Queries,” I explained that a query without an ORDER BY clause returns 

a set (or a multiset), whereas a query with an ORDER BY clause returns what standard SQL calls a 

cursor—a nonrelational result with order guaranteed among rows. In the context of the discussion in 

Chapter 2, the use of the term “cursor” was conceptual. T-SQL also supports an object called cursor 

that allows you to process rows from a result set of a query one at a time and in a requested order. 

This is in contrast to using set-based queries—normal queries without a cursor for which you manipu-

late the set or multiset as a whole and cannot rely on order.

I want to stress that your default choice should be to use set-based queries; only when you have a 

compelling reason to do otherwise should you consider using cursors. This recommendation is based 

on several factors, such as the following.

www.it-ebooks.info

---