In my previous articles I explained about What is SQL Server? - An Overview | SQL Server Tutorials, Quick Start Guide - Sql Server, About Cursor in Sql Server,
Naming Convention for Database Design, How to Count Number of Rows and Columns in a table with Query?, Difference between MS SQL 2005 & SQL Server 2000, SQL Commands - Create, Drop and Alter Database in SQL Server 2008 etc.
In this article I will explain about TSQL Stored Procedure and User Defined Functions.
What is Stored Procedure?
"A stored procedure in SQL Server is similar to a procedure in other programming languages, Its a precompiled collection of Transact-SQL statements stored under a name and processed as a unit."
- It can accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.
- It can contain programming statements that perform operations in the database, including calling other procedures.
- It can return a status value to a calling procedure or batch to indicate success or faliure (and the reason for failure)
So, SQL Server supplies stored procedures for managing SQL Server and displaying information about database and users.
Benefits of stored procedures
- Modular programming
- Faster Execution
- Reduction in network traffic
- Efficient reuse of code and programming abstraction
- Can be used as a security mechanism (Grant users permission to execute a stored procedure independently of underlying table permissions)
Stored Procedure Example
create procedure sp_ShowEmpDetails
select Name from Employee
To execute a stored procedure use the execute command:
execute sp_ShowEmpDetails 'meerut'
Please leave your comments, suggestions and queries about this post in the comment sections in order for me to improve my writing skills and to showcase more useful posts. Thanks for reading! :)