A stored procedure in Sqlserver 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 scripting or even from some other stored procedures.
The communication between SqlServer Stored procedure and client application happens through parameters and return values. The below picture shows that:
|Figure 1: SqlServer Proc. & External App. Communication|
Client applications, optionally sends data to the stored procedure and stored procedures performs processing based on the data sent by the client application. After the stored procedure finished its work it packs data back (Again optional) to its caller through 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 these stuff in this article.
2. Creating & EXEC Stored Procedure in Sql
You can create a stored procedure in the 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 the “EXEC” followed by the procedure name.
The structure of the stored procedure is shown in Fig.2. While creating a procedure, the name of the procedure is given first followed by one or more parameters passed to it. The parameters can be optional. After the parameter, the procedure body will be written to perform the required operations. The body can have local variables declared in it and these variables are local to the procedures (i.e.) they can be viewed only inside the procedure body.
|Figure 2: Basic Skeleton of SQLServer Stored Proc|
In the Fig.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 procedure body. Note that it will be good practice to create and execute the procedure with the schema name qualifier.
|Figure 3: A Simple Stored Proc. Example|
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 etc.,. 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 below given video, shows creating and executing a simple stored procedure using SQL Server Management Studio:
Video 1: Creating a Simple Stored Procedure
3. Declaring Variable in Procedure Body
You can declare variable local to the procedure body and these variables are visible inside the body of the procedure. The good practice is declaring the variables in the beginning of stored procedure body as shown in Fig.2. But, you can declare variables anywhere in the body of the stored procedure. Have a look at the Sample procedure given below:
|Figure 4: Declaring Variables in Stored Proc.|
In the above procedure, you can see variables Name1 and Name2 declared in the beginning of the procedure (Marked as 1). Here you can note that multiple variables are declared in a single line and each variable declaration is separated by a comma. Also note that the variable has prefix @. In the procedure body you can declare 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 set statement is used and this is shown in example as marked statements 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 above procedure is shown below:
|Figure 5: Executing the Procedure SayHello2|
OK. 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 it with comma. The below shows this:
|Figure 6: Multiple Variable Assignments in Single Statement|
4. 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 action inside the body of it. For example, let us create a procedure that will accept City and State from caller and tells how many authors belong to the passed-in City and State. The procedure will query the Authors table of the Pubs database to perform this author count. To have these database, Google it, or download SQL script from the SQL2005 page if this Web site. Below is the example procedure:
|Figure 7: Executing Procedure with Parameters|
In the above example procedure, the procedure accepts two parameters named @State and @City and this is marked as 1 in the above 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 authors. The parameters passed-in are used to form “WHERE” clause of the select query that count number of authors. This is marked as 2 in the screen shot. Finally the counted value is printed to the output window through the Print statement and the output is marked as 5.
There are two way to execute the procedure. The first way, marked as 3, shows passing the parameters as comma separated list after procedure name. In our example we are passing the values CA, Berkeley separated by 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 what 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 below 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 same as the one created in the previous screenshot and only the parameters as shifted. That is Parameter @City kept first and @State kept next with default value. The default parameter is marked as 1 in the below screen shot. Look at the Procedure execution (2, 3) and in the first execution (3) we passed both the parameters. In this case, the passed-in 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 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.
Figure 8: SQL Stored Procedure with Default Parameters
5. SQL stored procedure returning data
The three important ways of sending the data to the caller of the stored procedure is 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.
5.1 SQL Stored Procedure Return statement
In this technique, the procedure assigns value to a local variable and returns that. A procedure can directly return a constant value also. In below example, we created a procedure that returns Total number of author. When you compare this procedure with the previous one, you can see the Print statement is replaced by the return statement.
|Figure 9: SQL Stored Procedure with return statement|
OK. Now let us see how we can 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 print statement you can use Select statement like “Select @RetValue as OutputValue”. The below code snippet shows both the way of checking the procedure return value:
|Figure 10: Exec Stored procedure with return statement|
5.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” while creating the procedure. When a parameter is specified as output parameter, the procedure body should assign a value to it. Have a look at the Procedure example given below:
|Figure 11: Stored Procedure with Output Parameter type|
In the above procedure, two output parameters named @TotalAuthors, @TotalNoContract is 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 that is assigned inside the body of the procedure. The below picture shows executing the Procedure and retrieving the values through the output parameter:
|Figure 12: Exec stored procedure with output parameter|
In the above 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, @OutputVar2 are passed to the procedure (Marked as 2). Note that while passing the variables the keyword output is specified here also. Once the procedure is successfully executed the values returned through the output parameters are printed to the messages window and this is marked as 3.
5.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 below example the stored procedure accepts a parameter called @AuthID and queries the table Authors by filtering the records returned by using this @AuthId parameter. The Select statement is marked as 1 in the below 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 Fig.6 which shows returning data through select involving the computed variables.
|Figure 13: Select from stored procedure|
A stored procedure is powerful compare to a function in the front end doing the same thing. Since the stored procedure gets executed in the server itself, the data exchange in huge volumes between serve and client application (for certain computations) can be avoided. In this article, combining the data return techniques (For Example- Procedure returning data through return statement as well as output parameter) is not shown. But you can learn that yourself. Bye for now.