Accessing and Manipulating SQL Server Data

Accessing and Manipulating SQL Server Data

Access and manipulate data from a Microsoft SQL Server database by creating and using ad hoc queries and stored procedures.

You might be a bit surprised to find a Microsoft SQL Server objective on a Visual C# .NET certification exam, but it really makes perfect sense. Many Visual C# .NET applications require a database to enable them to store data on a permanent basis, and SQL Server is one of the best databases to use with the .NET framework. As you'll see later in this chapter, an entire namespace (System.Data.SqlClient) is devoted to efficient communication between .NET applications and SQL Server.

The objects in System.Data.SqlClient, though, won't do you any good unless you understand the language used to communicate with SQL Server, Transact SQL (T-SQL). T-SQL is Microsoft's implementation of SQL (Structured Query Language), which is defined by a standard from the American National Standards Institute (ANSI).

The core of T-SQL is based on the ANSI SQL-92 standard. SQL-92 defines a query-oriented language in which you submit queries to the database and get back a resultset consisting of rows and columns of data. Other queries cause changes to the database (for example, adding, deleting, or updating a row of data) without returning any resultset.

You can submit T-SQL to a SQL Server database for processing in two ways. First, you can write ad hoc queries, SQL statements that are executed directly. Second, you can write stored procedures, SQL statements that are stored on the server as named objects. Stored procedures can also include complex programming logic. The .NET Framework includes facilities for running both ad hoc queries and stored procedures.

Using Ad Hoc Queries

Ad hoc T-SQL queries provide an extremely flexible way to retrieve data from a SQL Server database or to make changes to that database. In this section, I'll show several ways to send an ad hoc query to SQL Server. Then you'll learn the basics of the four main T-SQL statements that help manipulate SQL Server data:

• SELECT statements enable you to retrieve data stored in the database.

• INSERT statements enable you to add new data to the database.

• UPDATE statements enable you to modify data already in the database.

• DELETE statements enable you to delete data from the database.

Running Queries

When learning T-SQL, it's useful to be able to send queries to a SQL Server database and to see the results (if any) that the server returns. You should be familiar with the many ways that are available to communicate with SQL Server. I'll show you four of them in this section:

• Using the Visual Studio .NET Integrated Development Environment (IDE)

• Using OSQL

• Using the SQL Query Analyzer

• Using a Visual C# .NET Application

Using the Visual Studio .NET IDE

When you just need to run a query in the course of working with a project, you can run it directly from the Visual Studio .NET IDE. Step-by-Step 1.12 shows you how.

When you run the query, Visual Studio .NET sends the SQL statement to the SQL Server that is specified by the database connection you chose in step 3. The server then processes the query (this particular query tells it to return all columns in all rows of the Employees table) and sends the results back to the client (in this case, Visual Studio .NET). The IDE then displays the results, formatted as a grid.

SELECT * FROM Employees

SELECT     *

FROM         dbo.Employees

The View Designer in Visual Studio .NET displays up to four panes. From top to bottom, the panes are as follows:

• The Diagram pane, which displays the tables involved in the query and the relationships between these tables, as well as all the columns that the tables contain.

• The Grid pane, which shows the columns that have been selected as part of the query, as well as additional sorting and filtering information.

• The SQL pane, which shows the actual SQL statement that will be executed.

• The Results pane, which shows the results (if any) after the query has been executed.

The View toolbar includes buttons that you can use to hide or show any of these four panes. For this chapter, you need only the SQL pane and the Results pane.

Using OSQL

A second option for executing ad hoc queries is to use one of the utilities that ships as a part of SQL Server, such as OSQL. OSQL is a command-line utility that can execute SQL Server queries (see Step-by-Step 1.13).

I chose a slightly different query for the OSQL session than I used in Step-by-Step 1.12. The SELECT query in Step-by-Step 1.13 specifies two columns from the table (FirstName and LastName), telling SQL Server to return only the contents of those two columns. If you execute SELECT * FROM Employees in OSQL, you might get a bit of a shock because the Employees table includes a bitmap image column, and the contents of that column will fill a command session with junk characters.

Using the SQL Query Analyzer

Although OSQL can be convenient for quick queries, it doesn't offer much in the way of tools. SQL Server also offers a full-featured query environment called SQL Query Analyzer (see Step-by-Step 1.14).

The SQL Query Analyzer offers an extremely flexible environment for running ad hoc queries. The features of the SQL Query Analyzer include the following:

• The ability to have multiple query windows open at the same time.

• An Object Browser in which you can see the structure of SQL Server objects

• Performance analysis tools

• Templates for common queries

For more information on using the SQL Query Analyzer, refer to SQL Server Books Online, the help file installed as part of SQL Server.

Using a Visual C# .NET Application

As a final alternative for executing ad hoc queries, you can build your own Visual C# .NET form to execute any query. Step-by-Step 1.15 shows you how.

You can learn about the ADO.NET objects that this example uses in Appendix A, "ADO.NET Basics." For now, I'll give a quick preview of the objects I just used:

• The SqlConnection object represents a connection to a database.

• The SqlCommand object represents a single query that you can send to the server.

• The DataSet object represents the results of one or more queries.

• The SqlDataAdapter object acts as a pipeline between the SqlConnection and DataSet objects.

The code in Step-by-Step 1.15 uses these objects to retrieve data from SQL Server to the data set, and it uses the SQL statement that you typed in to know which data to retrieve. It then uses complex data binding to display the results on the user interface in the DataGrid control.

The SELECT Statement

Now that you know a variety of ways to execute ad hoc queries, it's time to dig into the T-SQL language to see some of the possible queries, starting with the SELECT statement.

The most basic SQL statement is the SELECT statement, which is used to create a resultset. In skeleton form, a SELECT statement looks like this:

SELECT field_list

FROM table_list

WHERE where_clause

GROUP BY group_by_clause

HAVING having_clause

ORDER BY sort_clause

Each of these lines of code is called a clause. The SELECT and FROM clauses are required, and the rest are optional. Here's an example of a SQL statement containing only the required clauses:

SELECT OrderID, CustomerID

FROM Orders

The resultset for this statement contains the values of the OrderID and CustomerID fields from every record in the Orders table.

The SELECT clause can be used to obtain results other than just lists of fields. You've already seen the shortcut for all fields:

SELECT *

FROM Orders

You can also perform calculations in the SELECT clause:

SELECT OrderID,

CAST(ShippedDate - OrderDate AS integer) AS Delay

FROM Orders

The expression ShippedDate - OrderDate calculates the number of days between the two dates. The CAST function tells SQL Server to return the result as an integer. If you try this example, you'll see the AS clause supplies a name for the calculated column. If you omit AS Delay, the query still works, but SQL Server returns the calculation without assigning a name to the column.

You're not limited to fields from a single table. For instance, you might try retrieving information from both the Customers and Orders tables by using this query:

SELECT OrderID, Customers.CustomerID

FROM Orders, Customers

Customers.CustomerID is what's known as a fully qualified name, because it specifies both the table name and the field name. This is necessary because both the Customers and the Orders tables contain fields named CustomerID, and you need to tell SQL Server which one you want to display.

If you try the previous query, though, you get more than 75,000 records back—many more than the number of orders in the database! This happens because although the query includes all the proper tables, it doesn't tell SQL Server how to relate those tables.

This sort of query is called a cross-product query. SQL Server constructs the resultset by including one row in the output for each row in each combination of input table rows. That is, there's an output row for the first order and the first customer, for the first order and the second customer, and so on.

A more useful query, of course, would match each order with the corresponding customer.

That's the job of the INNER JOIN keyword. INNER JOIN tells SQL Server how to match two tables. Here's how the query looks for a fixed version of the original query:

SELECT OrderID, Customers.CustomerID

FROM Orders INNER JOIN Customers

ON Orders.CustomerID = Customers.CustomerID

This fixed query tells SQL Server to look at each row in the Orders table and match it with all rows in the Customers table where the CustomerID of the order equals the CustomerID of the customer. Because CustomerIDs are unique in the Customers table, using the preceding code example is the same as including only a single row for each order in the resultset.

The INNER JOIN keyword can appear more than once in a query if there are more than two tables to join. For example, the following query shows EmployeeIDs along with OrderIDs and CustomerIDs:

SELECT Orders.OrderID, Customers.CustomerID,

       Employees.EmployeeID

FROM Employees INNER JOIN

(Customers INNER JOIN Orders

ON Customers.CustomerID = Orders.CustomerID)

ON Employees.EmployeeID = Orders.EmployeeID

Note the use of parentheses to specify the order in which the joins should be performed.

The basic SELECT query allows you to see all the data in a table; for example:

SELECT * FROM Orders

That query returns every bit of data in the Orders table—every column, every row. You've already seen that you can use a field list to limit the number of columns returned:

SELECT OrderID, CustomerID, EmployeeID FROM Orders

But what if you want to see only some of the rows in a table? That's where the WHERE clause comes into the picture. You can think of a WHERE clause as making a simple, yes-or-no decision for each row of data in the original table, deciding whether to include that row in the resultset.

The simplest form of the WHERE clause checks for the exact contents of a field. Here's an example:

SELECT * FROM Orders

WHERE ShipCountry = 'Brazil'

This query looks at every row in the Orders table and determines whether the ShipCountry field contains the exact value Brazil. If it does, the row is included in the results. If it does not, the row is discarded.

However, WHERE clauses need not be exact. The following is also a valid SQL statement:

SELECT * FROM Orders

WHERE Freight > 50

In this case, you get all the rows where the amount in the Freight field is greater than 50.

Note, by the way, that in the first of these WHERE clause examples, Brazil appears in single quotation marks but 50 does not. This is simply a matter of syntax: Text and date data need quotation marks, but numeric columns do not.

You can combine multiple tests in a single WHERE clause. Here's an example:

SELECT * FROM Orders

WHERE ShipCountry = 'Brazil'

 AND Freight > 50

 AND OrderDate <= '12/31/97'

This retrieves all orders that went to Brazil, had more than $50 of freight charges, and were shipped before the end of 1997.

The entire WHERE clause must be a single logical predicate. That is, after all the pieces are evaluated, the result must be a true or false value. Rows for which the WHERE clause evaluates to true are included in the results; rows for which it evaluates to false are excluded.

You can also use wildcards in a WHERE clause. Consider this simple SELECT statement:

SELECT * FROM Customers

WHERE CustomerID = 'BLONP'

If you run that query, you find that it returns the record for Blondel pere et fils, the customer that is assigned the CustomerID BLONP. So far, that's easy. But what if you remember that the CustomerID starts with B, but not what it is exactly? That's when you'd use a wildcard:

SELECT * FROM Customers

WHERE CustomerID LIKE 'B%'

The % wildcard matches zero or more characters, so the result of this query is to retrieve all the customers whose CustomerIDs begin with B. Note the switch from = to LIKE when using a wildcard. (If you searched for CustomerID = 'B%', you'd only find a customer with that exact ID.) Now suppose you almost remember the CustomerID, but not quite: Is it BLOND or BLONP? Try this query:

SELECT * FROM Customers

WHERE CustomerID LIKE 'BLON_'

The _ wildcard matches precisely one character—so that would match BLONA, BLONB, and so on. If you're sure that it's either D or P, you can try the following:

SELECT * FROM Customers

WHERE CustomerID LIKE 'BLON[DP]'

The [DP] is a character set wildcard. The square brackets tell SQL Server to match any one of the characters listed in the set. You can also use a dash in a character set to indicate a range:

SELECT * FROM Customers

WHERE CustomerID LIKE 'BLON[D-P]'

This matches BLOND, BLONE, and so on, through BLONP. You can also invert a character set by using the ^ character; for example:

SELECT * FROM Customers

WHERE CustomerID LIKE 'BLON[^A-O]'

This matches BLONP, BLONQ, and so on, but not BLONA, BLONB, or anything else that would match the character set without the ^ character.

SQL is a set-oriented language; by default, the database engine is free to return the set of results in any order it likes. To guarantee a sort order, include an ORDER BY clause in your SQL statement. For example, to see the customers from Venezuela in postal code order, you could use this statement:

SELECT * FROM Customers

WHERE Country = 'Venezuela'

ORDER BY PostalCode

This example shows the basic ORDER BY clause: a field name to sort by. You can use two keywords to modify this: ASC, for ascending sort (the default), and DESC, for descending sort. Therefore, you could equally well write the previous SQL statement as follows:

SELECT * FROM Customers

WHERE Country = 'Venezuela'

ORDER BY PostalCode ASC

And you could get the customers sorted in reverse postal code order by using this statement:

SELECT * FROM Customers

WHERE Country = 'Venezuela'

ORDER BY PostalCode DESC

You're not limited to sorting by a single field. For example, you might want to see the entire customer list, sorted first by country and then by postal code within country:

SELECT * FROM Customers

ORDER BY Country, PostalCode

You can specify on a field-by-field basis the sort's order:

SELECT * FROM Customers

ORDER BY Country ASC, PostalCode DESC

This would sort by country in ascending order, and then by postal code in descending order within each country.

You can also calculate a sort. For example, you can sort the customers by the length of their company names:

SELECT * FROM Customers

ORDER BY Len([CompanyName])

Here the square brackets tell the Len() function that it's being passed a column name, and to retrieve that column value for each row as the input to the function.

A calculation need not have anything to do with the fields returned by the SELECT statement, as in this example:

SELECT * FROM Customers

ORDER BY 2+2

This is a perfectly valid SQL statement, although the effect is to put the records in whatever order the database engine decides it wants to use.

So far, all the SELECT statements you've seen in this chapter have returned results where each row corresponds to one row in the underlying tables. However, it's possible (and indeed common) to use SQL to return aggregate, summarized information.

For example, suppose you want to know how many customers you have in each country. Here's a query that gives you the answer:

SELECT Count(CustomerID) AS CustCount, Country

FROM Customers

GROUP BY Country

You can think of the GROUP BY clause as creating "buckets,"—in this case, one for each country. As the database engine examines each record, it tosses it in the appropriate bucket. After this process is done, the database engine counts the number of records that ended up in each bucket and outputs a row for each one. Figure shows the start of the resultset from this query.

You can use ORDER BY in conjunction with GROUP BY. For example, you could sort by the number of customers in each country:

SELECT Count(CustomerID) AS CustCount, Country

FROM Customers

GROUP BY Country

ORDER BY Count(CustomerID) DESC

Or you could sort by the country name:

SELECT Count(CustomerID) AS CustCount, Country

FROM Customers

GROUP BY Country

ORDER BY Country

In these SQL statements, Count() is an aggregate function—that is, a function that returns a result based on a number of rows. T-SQL supports a number of aggregate functions. Here are some of the most common:

• Count()— Returns the number of records.

• Sum()— Returns the total value of records.

• Avg()— Returns the average value of records.

• Min()— Returns the smallest record.

• Max()— Returns the largest record.

You can also group on more than one field. Here's an example:

SELECT Count(CustomerID) AS CustCount, Region, Country

FROM Customers

GROUP BY Region, Country

This statement sets up one bucket for each combination of region and country, and it categorizes the customers by both fields simultaneously.

So far, the GROUP BY statements you've seen have included all the records in the table. For example, consider this query:

SELECT ProductID,

Sum(Quantity) AS TotalSales

FROM [Order Details]

GROUP BY ProductID

ORDER BY Sum(Quantity) DESC

This query returns a resultset that has one row for each product found in the Order Details table, with the product ID and the total quantity of the product that was ordered. This query uses all the rows in the Order Details table to come up with its totals. There are two ways you can limit this to use only part of the table.

First, you can use a WHERE clause to limit the rows from the original query that will be included in the totals:

SELECT ProductID,

Sum(Quantity) AS TotalSales

FROM [Order Details]

WHERE Quantity > 10

GROUP BY ProductID

ORDER BY Sum(Quantity) DESC

This has the same effect as the first query, except that it just ignores any row in the Order Details table that has a quantity of 10 or under.

The other way to limit the results is by filtering the totals with a HAVING clause:

SELECT ProductID, Sum(Quantity) AS TotalSales

FROM [Order Details]

GROUP BY ProductID

HAVING Sum(Quantity) > 1000

ORDER BY Sum(Quantity) DESC

A HAVING clause filters on the results, rather than on the input. That is, this query sums everything from the Order Details table, and then it shows you only rows where the total is greater than 1,000.

You can also combine the two types of filtering, as in this example:

SELECT ProductID, Sum(Quantity) AS TotalSales

FROM [Order Details]

WHERE Quantity > 10

GROUP BY ProductID

HAVING Sum(Quantity) > 1000

ORDER BY Sum(Quantity) DESC

This query goes through the source table, sums up all the rows where the quantity is greater than 10, and then keeps only the rows where the total is greater than 1,000.

Note that WHERE and HAVING go in two different places in the SQL statement. The order of clauses is fixed, not optional.

The INSERT Statement

The purpose of the INSERT statement is to add a row or multiple rows to a table by executing a SQL statement. In its simplest form, the INSERT statement lists a target table and a set of values to insert. For example, this query (with the optional INTO keyword) adds a new row to the Order Details table:

INSERT INTO [Order Details]

VALUES (10248, 1, 12.00, 5, 0)

This simple form of the statement has two drawbacks. There are two drawbacks to this simple form of the INSERT statement. First, it's very difficult to tell which field is getting which piece of data; the values are inserted into the table fields in the order in which the fields show up in design view, but you need to remember (in this example) that the quantity is the fourth field. Second, if you use this format, you need to supply a value for every field. This is a problem when you want the default value for a field or when a field can't have data inserted into it (for example, an identity field, whose values are automatically generated by SQL Server). To get around these problems, a second format explicitly lists the fields for the target table:

INSERT INTO [Order Details]

  (OrderID, ProductID, UnitPrice, Quantity, Discount)

VALUES (10248, 2, 12.00, 5, 0)

Here, the first set of parentheses holds a column list, and the second set holds the values to insert. If a field has a default value, or can be null, or is an identity field, you can leave it out of the field list, as in this example:

INSERT INTO Products

  (ProductName, SupplierID, CategoryID)

VALUES ('Turnips', 25, 7)

This works even though no value is specified for most of the fields in the Products table. Also, you can rearrange the field list as long as you rearrange the value list to match:

INSERT INTO Products

  (SupplierID, ProductName, CategoryID)

VALUES (20, 'Lettuce',  7)

The INSERT statement isn't limited to inserting a single record. A second format inserts the results of a SELECT statement into the target table. For example, this query inserts a product from every supplier into the Products table:

INSERT INTO Products

  (SupplierID, ProductName, CategoryID )

SELECT SupplierID, 'Trout', 8

FROM Suppliers

This works by building the results of the SELECT statement and then putting each row returned by the SELECT statement into the target table. Of course, the columns still need to match up properly.

The UPDATE Statement

Another very useful SQL statement is the UPDATE statement. As you can probably guess, the purpose of an UPDATE query is to update data. For example, you could update a field in a record in the Northwind database by using this query:

UPDATE Customers

 SET ContactName = 'Maria Anderson'

 WHERE CustomerID = 'ALFKI'

In this query, the UPDATE statement introduces an UPDATE query. The SET keyword tells SQL Server what to update. In this case, it's setting a field equal to a literal value. The WHERE clause tells SQL Server which row in the table to update.

You're not limited to updating a single record. If the WHERE clause selects multiple records, they are all updated, as in this example:

UPDATE Customers

 SET Country = 'United States'

 WHERE Country = 'USA'

You can even update every row in a table by leaving out the WHERE clause:

UPDATE Products

 SET Discontinued = 1

This updates every row in the Products table—even those where the Discontinued field already has the value 1.

You can also update more than one field at a time by using an UPDATE query:

UPDATE Customers

 SET ContactName = 'Maria Anders', City = 'Berlin'

 WHERE CustomerID = 'ALFKI'

And you can update by using the result of an expression:

UPDATE Products

 SET UnitPrice = UnitPrice * 1.1

If only it were so simple to raise prices in real life!

Finally, you can update based on joined tables:

UPDATE Suppliers INNER JOIN Products

 ON Suppliers.SupplierID = Products.SupplierID

 SET Discontinued = 1

 WHERE Suppliers.Country = 'Italy'

This has the effect of discontinuing all the products that are imported from Italy.

The DELETE Statement

The DELETE statement removes data from a table. The rule for constructing a DELETE query is simple: Construct a SELECT query to select the records you want to delete and then change the SELECT keyword to DELETE. Remove any * identifier from the SELECT clause as well. That's it!

To avoid destroying existing data, let's set the stage for the DELETE statement by using the SELECT INTO statement to create a new table. For example, this statement creates a table named BadCustomers, with all the data from the existing Customers table:

SELECT * INTO BadCustomers

FROM Customers

Here's a SELECT query for selecting a single row from the new table:

SELECT * FROM BadCustomers WHERE CustomerID = 'GODOS'

Now change the SELECT * clause to DELETE:

DELETE FROM BadCustomers WHERE CustomerID = 'GODOS'

If you run this query, the specified row is deleted. There's no need for a WHERE clause if you want to get really extreme:

DELETE FROM BadCustomers

This statement deletes all the rows from the BadCustomers table.

Using Stored Procedures

When you use an ad hoc query to interact with SQL Server, the SQL statements in the query are completely transient—that is, they vanish as soon as you close whatever tool you've used to execute the query. In contrast, stored procedures are stored permanently on the SQL Server itself. Stored procedures have two main benefits. First, you can use them to save complex SQL statements for future execution so that you don't have to re-create them from scratch. Second, SQL Server compiles stored procedures so that they run faster than ad hoc queries.

In the following sections you'll learn how to create and run stored procedures. You'll also learn about parameters, which make stored procedures flexible, and the @@IDENTITY variable, which can supply useful information any time you use a stored procedure to insert data into a table that has an identity column.

Creating a Stored Procedure

T-SQL includes a CREATE PROCEDURE keyword to create stored procedures. You can run CREATE PROCEDURE statements from any interface that allows you to enter and execute T-SQL (see Step-by-Step 1.16).

You can see from Step-by-Step 1.16 that there are two separate executing steps in the process. Executing the CREATE PROCEDURE statement (which is itself an ad hoc query) is necessary to create the stored procedure. After that has been done, you can execute the stored procedure itself to return results.

Running Stored Procedures from the .NET Framework

Executing a stored procedure from the .NET Framework is very similar to executing an ad hoc query. The difference is that when you execute a stored procedure from the .NET Framework, you supply the name of the stored procedure instead of the actual SQL code as the CommandText property of a SqlCommand object and set the CommandType property to CommandType.StoredProcedure (see Step-by-Step 1.17).

Stored procedures are not limited to containing SELECT statements. You can place any SQL statement inside a stored procedure. For example, you might use this SQL statement to create a stored procedure to update the Customers table:

CREATE PROCEDURE procExpandCountry

AS

UPDATE Customers

 SET Country = 'United States'

 WHERE Country = 'USA'

When a stored procedure doesn't return a resultset, you need to use a slightly different code structure to execute it (see Step-by-Step 1.18). Guided Practice Exercise 1.1 offers additional practice in this technique.

You can use the ExecuteNonQuery() method of the SqlCommand object to execute any ad hoc query or stored procedure that doesn't return any results.

Using Parameters in Stored Procedures

The examples that you've seen so far in this chapter don't begin to tap the real power of stored procedures. SQL Server supports parameterized stored procedures, which enable you to pass information to the stored procedure at runtime (you can think of these as the T-SQL analogue of Visual C# .NET methods). For example, this SQL statement defines a stored procedure that returns the total sales for a particular customer, with the CustomerID specified at runtime:

CREATE PROC procCustomerSales

  @CustomerID char(5),

  @TotalSales money OUTPUT

AS

  SELECT @TotalSales = SUM(Quantity * UnitPrice)

  FROM ((Customers INNER JOIN Orders

  ON Customers.CustomerID = Orders.CustomerID)

  INNER JOIN [Order Details]

  ON Orders.OrderID = [Order Details].OrderID)

  WHERE Customers.CustomerID = @CustomerID

In this SQL statement, both @CustomerID and @TotalSales are variables (called parameters in T-SQL). To use the stored procedure, you must supply a value for the @CustomerID parameter. The @TotalSales parameter is marked as an OUTPUT parameter; it returns a value from the stored procedure to the calling code.

In the .NET Framework, the SqlCommand object has a collection of parameters that enable you to manage parameterized stored procedures (see Step-by-Step 1.19).

In ADO.NET parameters are represented by SqlParameter objects. The code in Step-by-Step 1.19 uses two different forms of the constructor for SqlParameters. The first takes the parameter name, the parameter data type, and the size of the parameter; the second omits the parameter size (because the money type has a fixed size). The code works by setting the Value property of the @CustomerID parameter, executing the SqlCommand object, and then retrieving the Value property of the @TotalSales parameter.

Using the @@IDENTITY Variable

A SQL Server table can have a single identity column. An identity column is a column whose value is assigned by SQL Server itself whenever you add a new row to the table. The purpose of the identity column is to guarantee that each row in the table has a unique primary key.

If you're working with a table that contains an identity column, you are likely to add a new row to the table and then immediately retrieve the value of the identity column for the new row. SQL Server provides a variable named @@IDENTITY for just this purpose. The @@IDENTITY variable returns the most recently assigned identity column value.

Step-by-Step 1.20 shows how to use a stored procedure to insert a new row in a table and return the value of the identity column so that your code can continue to work with the new row.

Step-by-Step 1.20 uses the same code pattern as Step-by-Step 1.19. The variable names and control names are different, but the two Step-by-Step examples show a common pattern for using stored procedures in code: