Stored Procedure in T-SQL

Stored Procedures are block of codes in T-SQL. It is a permanent database object in sql server. These codes are parsed and precompiled which saves execution time.In stored procedure we can multiple input parameters, multiple output parameters but can have only one return integer value.

In types of stored procedures we have main 4 types:

1. System Stored Procedures
2. User defined Stored Procedures
3. Extended Stored Procedures
4. CLR (Common Language Runtime)

In user defined stored procedures, we can categories according to parameters:

1. Stored Procedure with INPUT parameter
2. Stored Procedure without INPUT parameter
3. Stored Procedure with OUTPUT parameter
4. Stored Procedure without OUTPUT parameter
5. Stored Procedure with return 

We can not call store procedures but we have to execute stored procedures using EXEC or EXECUTE statement followed by stored procedure name with schema and parameters.

Syntax:
     CREATE PROC <procedure-name> <parameters>
      AS
      BEGIN
             <statements>
      END

    EXEC <procedure-name><parameters>

Stored procedure either can have BEGIN....END or GO at the end. We can execute stored procedure inside a stored procedure but we can not execute inside the function. We can also make function call inside stored procedure. Stored procedures are reusable and faster in execution compare to individual statements.

While creating stored procedures if we specify default values than missing parameter values will be passed from default value otherwise it will through an error in execution.

As per case of executing dynamic sql inside stored procedure requires executing stored procedure with writing permission.