SQL Tutorial

Body in Stored Procedure

The body part of a stored procedure is where you can put your business logic codes inside to execute it as you want it to do. A stored procedure body always begin with BEGIN and END keywords. Inside the body, you can put the declarative SQL statements or procedure call like calling other stored procedures. Beside that, you can also use procedure statement like IF, WHILE; you can declare local variables and use them inside the stored procedure. Here is the general stored procedure syntax :

<create procedure statement> ::=
   CREATE PROCEDURE <procedure name> ( [
 <parameter list> ] ) AS
      <procedure body>

<procedure body> ::= <begin-end block>

<begin-end block> ::=
   [ <label> : ] BEGIN <statement list> END [ 
<label> ]

<statement list> ::= { <body statement> ; }...

<statement in body::=
   <declarative statement> |
   <procedural statement>

<declarative statement> ::=
   <EXEC statement>              |
   <CLOSE statement>             |
   <COMMIT statement>            |
   <DELETE statement>            |
   <EXECUTE immediate statement> |
   <FETCH statement>             |
   <INSERT statement>            |
   <LOCK TABLE statement>        |
   <OPEN statement>              |
   <ROLLBACK statement>          |
   <savepoint statement>         |
   <SELECT statement>            |
   <SELECT INTO statement>       |
   <SET statement>               |
   <SET transaction statement>   |
   <start -transaction statement> |
   <UPDATE statement>

<procedural statement> ::=
   <BEGIN-END block>             |
   <EXEC statement>              |
   <CLOSE statement>             |
   <DELCARE condition statement> |
   <DELCARE cursor statement>    |
   <DELCARE handler statement>   |
   <DELCARE variable statement>  |
   <FETCH cursor statement>      |
   <flow control statement>      |
   <OPEN cursor statement>       |
   <SET statement>

With the BEGIN and END keword you can label the block of code inside the body. You can have one or more blocks, each block can be nested each other. Labeling the block has its own advantages. For example, it make your code more clear when you have mutilple blocks. Let's get your hand with some source code to demonstrate the stored procedure body.

Imagine we have employee table, in one day the table may have many records and it is very costly to get all the data from it and display them in our application. It would be nice if we can provide pagination feature for application to select needed records it needs to reduce the traffic between the database server and application server. Here is stored procedure to make it possible:

CREATE PROCEDURE GetEmployeePaged
 	@PageSize int = 10,-- pagesize
	@CurrentPage int = 1,-- current page no
	@ItemCount int output -- total employee found
AS
BEGIN
	-- declare local variables for pagination
	DECLARE @UpperBand INT,
			@LowerBand INT
 SET @LowerBand = (@CurrentPage - 1)* @PageSize
	SET @UpperBand = @CurrentPage* @PageSize + 1
	
	-- assign itemcount output parameter
	SET @ItemCount = (
		SELECT COUNT(employeeId) 
		FROM employees
	)

	-- create temporary table to store paged data
	CREATE TABLE #ALLROW(
		   RowID INT PRIMAY KEY IDENTITY(1,1),
			EmployeeId INT,
			Name VARCHAR(255),
			salary DECIMAL(7,2)
		)

	-- insert data into the temporary table
	INSERT INTO #ALLROW
	SELECT EmployeeId, Name, salary
	FROM employees

	-- get paged data
 SELECT * 
	FROM #ALLROW
	WHERE RowID  > @LowerBand AND RowID < @UpperBand
END

First in parameter list we have three parameters and their meanings are exactly what they are. @pagesize specifies number of record per page, @currentpage specifies the current page number and @itemcount specifies total record found. So we can get the employee record in the page 1 with 10 record per page by calling : 

EXEC GetEmployeePaged(10,1,@itemcount)

The next we declare two local variables. These variables is used inside the stored procedure for determining the start row and end row we will retrive the records. These variables' values are calculated based on the @pagesize and @currentpage.

Then we use SET statement to assign output parameter @itemcount to the total records of the employee.

Finally we create a temporary table to store the data, insert the data into the temporary table and retrieve the need records.

As you can see the stored procedure is very flexible, you can leverage it to deal with tough situation in database developement.

The next statement we will show you how to use local variable and use SET statement as shown in the example above in the stored procedure. Next tutorial: Local variable in stored procedure