Unit 7 Flashcards
a set of Transact-SQL (T-SQL) statements that is compiled and stored as a single database object for later repetitive use. It is the equivalent of a subroutine and a function in other programming languages
Stored Procedure
Anatomy of a Stored Procedure
- Composition
- Functionality
- Syntax
2 types of Composition
header & body
defines the name of the stored procedure, the input and output parameters, and some miscellaneous processing options.
header
contains one or more Transact-SQL statements to be executed at runtime.
body
The name of the procedure is a standard Transact-SQL identifier. The maximum length of any identifier is —— characters.
128
Stored procedures may contain up to —– input and output parameters.
2,100
The body of the stored procedure consists of one or more Transact-SQL statements. The maximum size of the body of the stored procedure is ——.
128MB
- An alternative way to send values from a stored procedure to the caller is to use a ——-
Return Values
assigning ———- to the parameters so that the user is not required to supply them. ———- are defined at the end of a parameter definition, behind the data types.
Default Values
You must type the name of the parameter and then assign a value to it. The parameter name must match its definition, including the – sign.
@
Types of Stored Procedures
- User-defined
- System
- Extended
- Temporary
- Global temporary
- Remote
- CLR
