Home » T-SQL Stored Procedures

T-SQL Stored Procedures

by Online Tutorials Library

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:

  1. IN – The procedure can reference the parameter. The procedure will overwrite the value of the parameter.
  2. OUT– The procedure cannot reference the parameter, but the procedure overwrites the parameter value.
  3. 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

Next TopicT-SQL-Sub queries

You may also like