SQL Server Stored procedure

Stored procedure is the batch of SQL statements which used to perform some specific business task.

In Stored procedures the tasks are written as a bunch of SQL Statements which performs some specific task and combined perform business logic.

Different SQL statement can be come under one execution plan which enhances the performance. The logic written in the stored procedure reside on server itself so the interaction between the client and server is minimal.

Stored procedures provide database abstraction i.e. keeps the table hidden from the user.

Error handling can be done in stored procedure. You can use DDL and DML statement in stored procedure.

Through stored the network traffic can be reduced.

Syntax for SP

1.      Create statement Create procedure A

2.      Declare parameters

3.      Begin

4.      Write the logic for achieving business logic.

5.      End.

SP is executed by exec command

Exec proc 1.

SQL Server uses various system stored procedure to handle different system operation which encapsulate the system table to be directly in use.

Stored procedure can have output parameters, output variable need to be declaring before executing the SP so the output value can be hold.