T-SQL Stored Procedures
The Stored procedure in Transact SQL is used to save time to write the code, again and again. It does this by storing the procedure in database and get the required output by passing the parameters.
Syntax:
Below is the syntax of stored procedure creation.
Parameter Optional: When we create a procedure then one or more parameters are passed into the procedure. There are 3 types of parameters in stored procedure:
- IN – The procedure can reference the parameter. The procedure will overwrite the value of the parameter.
- OUT– The procedure cannot reference the parameter, but the procedure overwrites the parameter value.
- IN OUT– The parameter is referenced by the procedure, and the procedure overwrites the value of the parameter.
declaration_section
declaration_section in the procedure is where we declare local variables in the section.
executable_section
In executable_section, the procedure where we enter the code for the procedure.
Example:
See the CUSTOMERS table which have the below records.
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Hamilton | 23 | Australia | 34000 |
2 | Warner | 34 | England | 22000 |
3 | Martin | 28 | China | 25000 |
4 | Twinkle | 30 | Turkey | 50000 |
5 | Tinu | 32 | Nepal | 45000 |
6 | Michal | 31 | Bhutan | 20000 |
7 | Harper | 20 | Bangladesh | 15000 |
The following command is the example, which will fetch all the records from the CUSTOMERS table in the testdb database.
The command will produce the below output.
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Hamilton | 23 | Australia | 34000 |
2 | Warner | 34 | England | 22000 |
3 | Martin | 28 | China | 25000 |
4 | Twinkle | 30 | Turkey | 50000 |
5 | Tinu | 32 | Nepal | 45000 |
6 | Michal | 31 | Bhutan | 20000 |
7 | Harper | 20 | Bangladesh | 15000 |