Procedural Transact-SQL

To perform processes that cannot be done using a single Transact-SQL statement, you can group Transact-SQL statements together in several ways:

  • Using batches

    A batch is a group of one or more Transact-SQL statements that are sent from an application to the server as one unit. Microsoft SQL Server executes each batch as a single executable unit.

  • Using stored procedures

    A stored procedure is a group of Transact-SQL statements that have been predefined and precompiled on the server. The stored procedure can accept parameters, and can return result sets, return codes, and output parameters to the calling application.

  • Using triggers

    A trigger is a special type of stored procedure. It is not called directly by applications. It is instead executed whenever a user performs a specified modification (INSERT, UPDATE, or DELETE) to a table.

  • Using scripts

    A script is a series of Transact-SQL statements stored in a file. The file can be used as input to the sqlcmd utility or SQL Server Management Studio Code editor. The utilities then execute the Transact-SQL statements stored in the file.

The following SQL Server features allow you control the use of multiple Transact-SQL statements at a time:

  • Variables

    Allow you to store data for use as input in a later Transact-SQL statement. For example, you can code a query that needs different data values specified in the WHERE clause each time the query is executed. You can write the query to use variables in the WHERE clause, and code logic to fill the variables with the proper data. The parameters of stored procedures are a special class of variables.

  • Control-of-flow statements

    Allow you to include conditional logic. For example, if the country is Canada, perform one series of Transact-SQL statements. If the country is U.K., do some other series of Transact-SQL statements.

  • Error handling

    Lets you customize the way SQL Server responds to problems. You can specify appropriate actions to take when errors occur, or display customized error messages that are more informative to a user than a generic SQL Server error.