T-SQL Programming (SQL Server 2008 R2)

Programming with T-SQL

• Stored Procedure
• A stored procedure is a pre-compiled collection of transact-sql statements
• A stored procedure encapsulates repetitive tasks
• A stored procedure can accept input parameters and returns status value to indicate success or failure
• A stored procedure can return multiple output parameters.
• T-SQL statements that make up the body of a stored procedure can perform database operations like INSERT, DELETE, UPDATE, and SELECT as well as call another stored procedure(s).
• To view the definition of stored procedure sp_helptext
• Drop a single stored procedure DROP PROCEDURE procedure _name
• Dropping multiple stored procedures DROP PROCEDURE procedure1, procedure2
• Advantages:
• Share application logic
• Shield database schema details
• Provide security mechanisms
• Improve performance
• Stored procedures implement many tasks as a series of T-SQL statements. Conditional logic can be applied to the results of the first T-SQL statements to determine which subsequent Transcat-SQL statements are executed.
• Reduce network traffic
• Rather than sending hundreds of Transact-SQL statements over the network, users can perform a complex operation by sending a single statement, which reduces the number of requests that pass between client and server.
• The maximum size of a stored procedure is 128MB depending on available memory.
• User Defined Functions
• Working with Cursors
• Cursors can be consider as named results sets which allow a user to move through each record one by one
• Triggers
• A trigger is a special type of stored procedure
• A trigger is:
• Associated with a table
• Invoked automatically
• Not called directly
• Treated as part of the transaction that fired it