Introduction
This article, the last in the Stored Procedure series, will focus on RETURN parameters. The first article in this series explained the benefits of using stored procedures as well as an example demonstrating a data grid being populated from one. The DOT NET aspx web page developed in C#. The second article covered INPUT parameters. Often, an INPUT is used to control a SQL WHERE clause. The third article discussed OUTPUT parameters. An OUTPUT is used to send a discreet value back to the application that executed it. In the Part 3 example, an OUTPUT was used to send back the IDENTITY of a newly created record.
RETURN
A RETURN parameter sends back a single value to the application that called it. Usually, the RETURN value is some type of success, failure, or error code. In this first example, a RETURN will indicate if the Person Contact table from Adventure Works contains any records. If there is one or more rows, the number five will be returned, if not a seven is sent back. Create the test procedure with the following TSQL code:
USE AdventureWorks;
GO
CREATE procedure Example4_1
AS
IF EXISTS(
SELECT TOP 1 *
FROM Person.Contact
)
RETURN 5
ELSE
RETURN 7;
There are a few differences between the RETURN and the other parameters (INPUT and OUTPUT) that were covered in the previous articles. The RETURN does not need to be declared, either above the AS statement or anywhere else. A RETURN value cannot be a NULL or pass back strings; only an INT can be returned. In addition, there can only be one RETURN parameter, unlike OUTPUT parameters, where there can multiple. Probably the most significant difference is that RETURN is a key TSQL word. When the SQL engine encounters it, as in the example above, the return value is passed back and the procedure ends. Any code after the return will not be executed or evaluated. The procedure is unconditionally exited. Therefore, it is not possible to pass a RETURN value from one subroutine and continue on to another subroutine. At the first encounter of a RETURN, the entire procedure ends. A RETURN can be located anywhere inside of a stored procedure. If no value is specified behind a RETURN, and the procedure completes successfully, a zero will be returned. To test the example, execute the following TSQL statements:
USE AdventureWorks;
GO
DECLARE @return_status int;
EXEC @return_status = Example4_1;
SELECT 'Return Status' = @return_status;
As shown below, a five should be returned indicating there is at least one row in the Person Contact table.
Executing this procedure and receiving the return value back from inside a DOT NET web application is very similar to working with an OUTPUT parameter. This example will display the RETURN value as a Label Text. Create a web page that includes a Label with the following code:
<%@ Page Language="C#" %>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Example 4-1</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<p><asp:Label ID="Label1" runat="server" Text="Label"></asp:Label> </p>
</div>
</form>
</body>
</html>
To execute the procedure, create a script statement and place it under the above html.
<script runat="server">
protected void Page_Load(object sender, EventArgs e)
{
System.Data.SqlClient.SqlConnection objConn = new System.Data.SqlClient.SqlConnection();
objConn.ConnectionString = "server=.; database=AdventureWorks; uid=sa; pwd=test;";
objConn.Open();
System.Data.SqlClient.SqlCommand objCmd = new System.Data.SqlClient.SqlCommand("Example4_1", objConn);
objCmd.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.SqlClient.SqlParameter pRetValue = objCmd.Parameters.Add("@Ret", System.Data.SqlDbType.Int);
pRetValue.Direction = System.Data.ParameterDirection.ReturnValue;
objCmd.ExecuteScalar();
objConn.Close();
Label1.Text = objCmd.Parameters["@Ret"].Value.ToString();
}
</script>
To start, a Connection is declared and opened using an SA connection string. In production though, SA should never be used because of the excessive permissions it owns. Next, the Command object is told it will be executing a stored procedure, and the name of the procedure. In the following line, DOT NET is told about the RETURN parameter. There are a couple of points worth making. The DOT NET parameter name “pRetValue” does not need to match the SQL name of @Ret. In addition, unlike INPUT and OUTPUT parameters, where the SQL name used inside the stored procedure must match the @ name in the code, RETURN parameters have no “real” name. Therefore, @Ret could be any word.
System.Data.SqlClient.SqlParameter
pRetValue = objCmd.Parameters.Add("@Ret",
System.Data.SqlDbType.Int);
In the next line of code, pRetValue is defined as the parameter type RETURN.
pRetValue.Direction = System.Data.ParameterDirection.ReturnValue;
Running the web page will display the Label with the value of five.
Below is Visual Basic for executing the stored procedure.
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
Dim objConn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
objConn.ConnectionString = "server=.; database=AdventureWorks; uid=sa; pwd=test;"
objConn.Open()
Dim objCmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("Example4_1",objConn)
objCmd.CommandType = System.Data.CommandType.StoredProcedure
Dim pRetValue As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@Ret",System.Data.SqlDbType.Int)
pRetValue.Direction = System.Data.ParameterDirection.ReturnValue
objCmd.ExecuteScalar()
objConn.Close()
Label1.Text = objCmd.Parameters("@Ret").Value.ToString()
End Sub
As a clarifying note, a RETURN does not have to interrupt code. Unlike the previous example, it can be the last line of a statement. The following statement is valid:
USE AdventureWorks;
GO
CREATE procedure Example4_2
AS
SELECT *
FROM Person.Contact;
RETURN @@ROWCOUNT;
This could be used to populate a data grid with the rows from the SELECT, and a Label displaying the total row count from the RETURN.
Conclusion
This series presented Stored Procedures as a data access method for DOT NET web pages. It also introduced several different types of parameters that can be used with a stored procedure, INPUT, OUTPUT, and RETURN. They can be used together, alone, or in any combination with each other. Alternatively, as in the first example in Part 1, none can be used. Several of the benefits of using stored procedures were explored, such as increased performance, increased security, and prevention of SQL injection attacks. In addition, MDAC problems associated with executing query stings may be prevented. In addition, stored procedures help code organization and reuse. As demonstrated in the examples, the learning curve for developing with Stored Procedure is very small. With all the benefits of stored procedures, the question usually comes up as to why everyone doesn’t use them for development. The top reason usually sited is the work boundary of a developer having to submit procedures to a DBA that takes too long to respond back. I have never heard of SQL or DOT NET disadvantage. Therefore, if your organization can take advantage of stored procedures, the benefits will very rewarding.
» See All Articles by Columnist Don Schlichting
