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.

sp, stored procedure, stored procedure example, tsql stored procedure, sql execute stored procedure, sql tutorial, sql query examples, sql server tutorials, sql server 2008 r2 management studio

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
@city varchar(20)
select Name from Employee
where City=@city

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! :)

Post a Comment