Procedures in SQL Server

Stored Procedures:
A stored procedure is a piece of programming code that can accept input parameters and can return one or more output parameters to the calling procedure or batch.
They can also return status information to the calling procedure to indicate whether they succeeded or failed.
SQL Server stored procedures provide excellent security for your database. You can grant rights to the stored procedure without granting rights to the underlying objects.
The SET NOCOUNT statement, when set to ON, eliminates the xxrow(s) affected message in the SQL Express Manager window. It also eliminates the DONE_IN_PROC communicated from SQL Server to the client application. For this reason the SET NOCOUNT ON statement, when included, improves the performance of the stored procedure.

Using the @@ Functions

Developers often refer to the @@ functions as global variables. In fact, they don’t really behave like variables. You cannot assign values to them or work with them as you would work with normal variables. Instead they behave as functions that return various types of information about what is going on in SQL Server.

The @@TranCount function is applicable when you are using explicit transactions. Transactions are covered later in this chapter. The BEGIN TRAN statement sets the @@TranCount to one. Each ROLLBACK TRAN statement decrements @@TranCount by one. The COMMIT TRAN statement also decrements @@TranCount by one. When you use nested transactions @@TranCount helps you to keep track of how many transactions are still pending.

The @@Identity function retrieves the new value inserted into a table that has an identity column.

The @@Error function returns the number of any error that occurred in the statement immediately preceding it.

When you build a stored procedure, a query plan is created. This query plan contains the most efficient method of executing the stored procedure given available indexes and so on.