Creating and Using Stored Procedure in SQL Server

Introduction

A Stored Procedure in SQL Server is an executable program unit that can be stored in the database as objects. These Stored Procedures can be executed from client application programs to get good performance. The Stored Procedure can be called from other scripts or even from some other Stored Procedure.

 

The communication between a SQL Server Stored Procedure and a client application happens using parameters and return values. The following picture shows that:

 

Client applications optionally send data to a Stored Procedure and Stored Procedures do processing based on the data sent by the client application. After a Stored Procedure has finished its work it packs data back (again optional) to its caller using various output techniques like:

1. Sending data via Output parameter type.
   
   
2. Sending data via return statement.
   
   
3. Sending data by select statement.

We will see all those in this article.

1. Creating an EXEC Stored Procedure in SQL

You can create a Stored Procedure in SQL Server Management Studio. Once the procedure is created it will be listed in the programmability node of the database in which the procedure create statement is executed. To execute the procedure use “EXEC” followed by the procedure name. 

 

The structure of the Stored Procedure is shown in Figure 2. When creating a procedure, the name of the procedure is supplied first, followed by one or more parameters passed to it. The parameters can be optional. After the parameter(s), the procedure body will be written to do the required operations. The body can have local variables declared in it and these variables are local to the procedures. In other words they can be viewed only inside the procedure body. 

 

 

In Figure 3 below, a simple Stored Procedure is shown skipping all the optional parts of the procedure. So to create a procedure we need a procedure name and at least a single statement as the procedure body. Note that it is a good practice to create and execute the procedure with the schema name qualifier.

 

 

 

The procedure body can have any kind of the SQL statements like creating a table, inserting one or more table row(s), setting the behavior of the database and so on. However, the procedure body restricts certain operations in it. Some of the important restrictions are listed below:

1. The body should not create some other Stored Procedure.
   
   
2. The body should not create a view.
   
   
3. The body should not create any triggers.
   

The following video shows the creation and execution of a simple Stored Procedure using SQL Server Management Studio:

Video 1: Creating a Simple Stored Procedure

2. Declaring Variable in Procedure Body

You can declare a variable local to the procedure body and these variables are visible inside the body of the procedure. The good practice is to declare variables at the beginning of the Stored Procedure body as shown in Figure 2. But, you can declare variables anywhere in the body of the Stored Procedure. Have a look at the sample procedure given below:

 

 

In the preceding procedure, you can see the variables Name1 and Name2 declared in the beginning of the procedure (Marked as 1). Here you can see that multiple variables are declared in a single line and each variable declaration is separated by a comma. Also note that the variable has the prefix @. In the procedure body you can declare a variable wherever you want and in the example the variable @Name3 is declared towards the end of the procedure body. To assign a value to a declared variable a set statement is used and this is shown in the example marked as statement 3. Unlike declaring more than one variable in a single line, only one set statement is allowed in a line. The result of executing the preceding procedure is shown below: 

 

 

Oay. How do I assign multiple values in a single statement in the procedure body? Well. The answer is using the “Select Var=value” pairs. And you can use these pairs by separating them with a comma. The following figure shows this:

 

 

3. SQL Stored Procedure parameters

In the previous examples, we saw creating a simple Stored Procedure and executing it. A procedure can accept parameters and the caller of the procedure passes values (but not always; we will see that soon) to it. Based on the passed in value, the procedure takes relevant actions inside the body of it. For example, let us create a procedure that will accept City and State from the caller and return how many authors belong to the supplied City and State. The procedure will query the Authors table of the Pubs database to perform this author count. To get these databases, Google it, or download the SQL script from the SQL2005 page. The following is the example procedure: 

 

 

 

In the preceding example procedure, the procedure accepts two parameters named @State and @City and this is marked as 1 in the preceding screen shot. The data type matches the type defined in the table definition for convenience. The procedure body has the internal variable @TotalAuthors and this variable is used to display the number of authors. The parameters passed-in are used to form a “WHERE” clause of the select query that counts the number of authors. This is marked as 2 in the screen shot. Finally the counted value is printed to the output window using the Print statement and the output is marked as 5. 

 

There are two ways to execute the procedure. The first way, marked as 3, shows passing the parameters as comma-separated list after the procedure name. In our example we are passing the values CA and Berkeley separated by a comma. These values are collected by the procedure parameter variables @State and @City. In this method, the parameter passing order is important. This first technique is called “Passing Argument by Ordinal Position”. In the second way, marked as 4, after the procedure name, the parameters are directly assigned and in this case the order is not important. This second technique is known as “Passing Named Arguments”. This is explained in the following video:

Video 2: Executing procedure with Parameters.

The screen shot below, shows creating a Stored Procedure with default parameters and executing it. The procedure is the same as the one created in the previous screenshot and only the parameters are shifted. That is the parameter @City kept first and @State kept next with the default value. The default parameter is marked as 1 in the following screen shot. Look at the procedure execution (2, 3) and in the first execution (3) we passed both the parameters. In this case, the provided parameter "UT" replaces the default value "CA" (shown as 4). In the second execution we passed only one argument value "Covelo" for the parameter @City and the parameter @State takes the default value "CA". It is a good practice to have all the default parameters towards the end of the parameter list. Otherwise, the execution marked as 2 is not possible and you should go with Passing Named arguments. 

 

 

 

4. SQL Stored Procedure returning data

 

The three important ways to send the data to the caller of the Stored Procedure are listed below:

1. Stored Procedure Returning value.
2. Stored Procedure Output parameter.
3. Select from Stored Procedure.

We will look at each technique one by one.

4.1 SQL Stored Procedure Return Statement

In this technique, the procedure assigns a value to a local variable and returns that. A procedure can directly return a constant value also. In the following example, we created a procedure that returns the total number of authors. When you compare this procedure with the previous one, you can see the Print statement is replaced by the return statement. 

 

Okay. Now let us see how to execute the procedure and print the value returned by the procedure. The procedure execution requires declaring a variable and printing that after the procedure execution. Note that instead of a print statement you can use a Select statement like “Select @RetValue as OutputValue”. The following code snippet shows both ways to check the procedure return value:

 

 

 

4.2 SQL Stored Procedure OUTPUT parameter

The return statement can be used to return a single variable and that we saw in the previous example. Using the Output parameter type a procedure can send one or more variable values to the caller. The output parameter is indicated by the keyword “Output” when creating the procedure. When a parameter is specified as an output parameter, the procedure body should assign a value to it. Have a look at the procedure example given below:

 

In the preceding procedure, two output parameters named @TotalAuthors and @TotalNoContract are specified in the parameters list. These variables are assigned values inside the procedure body. When we use output parameters the caller can see the value assigned inside the body of the procedure. The following picture shows the execution of the procedure and retrieve the values using the output parameter:

 

 

 

In the preceding script, two variables are declared to see the values packed by the Stored Procedure in the output parameter and this is marked as 1. Then the procedure is executed by supplying the normal parameter values CA and Berkeley. The third and fourth parameters are output parameters and hence the declared variables @OutputVar1 and @OutputVar2 are passed to the procedure (marked as 2). Note that when passing the variables the keyword output is specified here also. Once the procedure is successfully executed the values returned using the output parameters are printed to the messages window and this is marked as 3.

 

4.3 Select from Stored Procedure

 

The Select…From technique is used to return a set of values in the form of table of data (RecordSet) to the caller of the Stored Procedure. In the following example the Stored Procedure accepts a parameter called @AuthID and queries the table Authors by filtering the records returned using this @AuthId parameter. The Select statement is marked as 1 in the following picture decides what needs to be returned to the caller of the Stored Procedure. When executing the Stored Procedure “Author Id” is passed in (shown as 2). The result of executing the Stored Procedure is marked as 3. The example procedure here always returns only one record or none. But a Stored Procedure does not have any restriction to return more than one record. Look at Figure 6 that shows the return of the data using a select involving the computed variables.

 

 

 

 

5. Conclusion

A Stored Procedure is powerful compared to a function in the front end doing the same thing. Since the Stored Procedure is executed in the server itself, the data exchange is in huge volumes between the server and client application (for certain computations) can be avoided. In this article, combining the data return techniques (for example, a procedure returning data using a return statement as well as an output parameter) is not shown. But you can learn that yourself. Bye for now.