The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P132: Congratulations on your journey to become certified in SQL Server 2008. This book will help prepare you for your exam and give you a practical view of working with SQL Server 2008. | Implementing Objects Chapter 14 637 StarName StarType Deneb White Supermassive Giant Pollux Orange Giant Sun Yellow dwarf Figure Creating a View Using SQL Server Management Studio Creating Stored Procedures Stored procedures are Transact-SQL statements that perform one or more actions and are saved in the database with a name. Stored procedures used widely to encapsulate the logic of your database system can accept parameters and return values. Stored procedures are the only database object that can update data by executing DML statements. For example you may write a stored procedure named AddCustomer that accepts a CustomerName EMailAddress and PhoneNumber 638 Chapter 14 Implementing Objects logic within this stored procedure can check that the potential customer s details are valid insert a new row into the Customers table using parameter values supplied and then return the CustomerlD of the newly created customer. To create a stored procedure use the CREATE PROCEDURE statement syntax shown in Example CREATE PROCEDURE keywords can be shortened to CREATE PROC. To change the definition or options of a stored procedure use the ALTER PROCEDURE or ALTER PROC statement. Example CREATE PROCEDURE Statement-Syntax CREATE PROCEDURE schema_name .stored_procedure_name procedure_ number @parameter1_name parameter1_data_type default_parameter_value OUT OUTPUT READONLY @parameter2_name parameter2_data_type. . . WITH ENCRYPTION RECOMPILE EXECUTE AS AS BEGIN transact_sql_statements END Stored procedures can be grouped into logical named groups. Each procedure within a group will have a unique procedure_number while the entire group can be referred to using the procedure_name. The entire procedure group can be dropped at once using the DROP PROCEDURE statement. To use a single procedure you can omit the procedure_number. In this case procedure_name will always be used to refer to it. Parameters are named variables passed into the procedure. .
