Instructor Inputs - Session 11

Instructor Inputs Session Overview This session includes section two and three of Chapter 7 of the Student Guide. Slide 1 Querying and Managing Data Using SQL Server 2005 Objectives In this session, you will learn to: Implement stored procedures Implement functions Ver. 1.0 Session 11 Slide 1 of 19 Start the session by sharing the objectives with the students. In this session, the students will learn about usage of stored procedures and functions. They will learn how to create and manage these database objects. ¤NIIT Instructor Inputs 11.3 Slide 2 Querying and Managing Data Using SQL Server 2005 Creating Stored Procedures Stored procedures: Are created using the CREATE PROCEDURE statement Are executed using the EXECUTE PROCEDURE statement Syntax: CREATE PROCEDURE proc_name AS BEGIN sql_statement1 sql_statement2 END Let’s see how… Ver. 1.0 Session 11 Slide 2 of 19 In this topic, you need to explain the concept of stored procedures to the students. Further, you will discuss the benefits of procedures as modularity, speed, security, reduced network congestion and consistency of usage across applications and users. Tell the students that since stored procedures have so many benefits, all operations and transactions from the client such as queries, updation, insertion, and deletion of rows are done using stored procedures. Even if the insert, update, delete, or query operation is very simple, a stored procedure must be created. This improves performance of the application. Hence, programmers simply execute the stored procedures, which are stored at the backend instead of sending SQL statements from the client. If the definition of the stored procedure needs to be modified, then use the ALTER PROCEDURE statement. You can use the examples given in the Student Guide to clarify the concept to the students. Further, you can execute the following statements to explain the concept: When designing an application, stored procedures can significantly reduce the network requirements. Use stored procedures for long, complicated, and frequently repeated queries. This reduces the traffic from the client to the server because only the stored procedure name and its associated parameters are passed across the network to the server, where it is executed. In addition, multi-step queries that perform additional filtering or processing based upon the response to initial queries, run much more efficiently as a stored procedure. By using a stored procedure, it is not necessary to pass the results of the initial query to the client in order that a second query can be passed to the server. 11.4 Instructor Inputs ¤NIIT The sp_depends stored procedure can be used to find out the dependencies of a stored procedure. The syntax of the sp_depends stored procedure is: sp_depends object_name Slide 3 Querying and Managing Data Using SQL Server 2005 Creating Stored Procedures (Contd.) Stored procedure: Is modified using the ALTER PROCEDURE statement Syntax: ALTER PROCEDURE proc_name Is deleted using the DROP PROCEDURE statement Syntax: DROP PROCEDURE proc_name Let’s see how… Ver. 1.0 Session 11 Slide 3 of 19 In this topic, you will tell the students how to alter and drop a stored procedure. You can use the examples given in the Student Guide to demonstrate the concept to the students. Slide 4 Querying and Managing Data Using SQL Server 2005 Just a minute Which command will you use to modify the procedure? Answer: ALTER PROCEDURE Ver. 1.0 Session 11 Slide 4 of 19 ¤NIIT Instructor Inputs 11.5 ... - tailieumienphi.vn