This is the second article in a series focused on working with stored procedures in ADO.NET. In this article we mainly focus on passing NULLS to a stored procedure, working with the OUTPUT parameter, and retrieving RETURN values of stored procedures.
A downloadable file for this article is available here.
If you are new to stored procedures (or in accessing them using ADO.NET) in SQL Server, I strongly suggest you go through my first article before proceeding further.
To work with the stored procedures in this article, you need the simple extra tables “emp” and “dept” within the “Northwind” database. The structures of those two tables are available in the first article of this series.
How to pass a NULL to a stored procedure parameter using ADO.NET from ASP.NET
Before going into the ADO.NET code, we need to create simple stored procedure in SQL Server. Using “Query Analyzer,” execute the following script in the “Northwind” database.
CREATE PROCEDURE dbo.sp_Emp_add
(
@empno int,
@ename varchar,
@sal float,
@deptno int
)
AS
INSERT INTO emp
(
empno,
ename,
sal,
deptno
)
VALUES
(
@empno,
@ename,
@sal,
@deptno
)
RETURN
The above stored procedure simply adds a new row to the table “emp” based on the parameters you provided. The stored procedure is named “sp_Emp_add.” Now, we need to use ADO.NET to access the same in ASP.NET.
The steps would be very similar to the steps I discussed in my first article in this series. So, I shall start directly with Visual Studio. Add a new web form (call it “SendingNull2SPParam”) to an already existing project (as specified in my previous article). Add a button named “Execute SP with Parameter value as NULL.”
After designing the form, switch to the code and add the following line at the top.
Imports System.Data.SqlClient
Add the following code to your “Execute SP with Parameter value as NULL” button:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cn As New SqlConnection("Data Source=.;initial catalog=Northwind;user id=sa")
Dim cmd As New SqlCommand
With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_emp_add"
.Parameters.Add("@empno", 2001)
.Parameters.Add("@ename", System.DBNull.Value)
.Parameters.Add("@sal", 3400)
.Parameters.Add("@deptno", 30)
.Connection = cn
.Connection.Open()
.ExecuteNonQuery()
.Connection.Close()
.Dispose()
End With
End Sub
Set the start page, execute your application (by pressing F5) and click on the button “Execute SP with Parameter value as NULL.” Once it executes successfully, check your table “emp.” You should be able to see a new row without “ename” (or with NULL)!
The most important tip from the above code is that I used “System.DBNull.Value.” This is the most efficient way to deal with NULL values while passing parameters to stored procedures.
What is an OUTPUT parameter really? We generally use a parameter to send a value into the stored procedure. The OUTPUT parameter of a stored procedure helps us send a value back to the calling environment (in this case, it would be ADO.NET) from within the stored procedure.
The matter can be simplified as follows:
INPUT parameter – value gets passed from the calling environment (ADO.NET) to the stored procedure.
OUTPUT parameter – value gets passed from the stored procedure to the calling environment (ADO.NET).
Before going to the ADO.NET code, we need to create a simple stored procedure in SQL Server with a simple OUTPUT parameter. Using “Query Analyzer,” execute the following script in the “Northwind” database.
CREATE PROCEDURE dbo.sp_emp_getEname
(
@empno int,
@name varchar(20) OUTPUT
)
AS
SELECT @name = ename FROM emp
WHERE empno=@empno
RETURN
The above stored procedure simply retrieves a row from the “emp” table based on the “empno” we provide. The “ename” is retrieved and placed in our OUTPUT parameter “@name” (which can be used in our web form). The stored procedure is named “sp_Emp_getEname.” Now we need to use ADO.NET to access the same in ASP.NET (proceed to the next section).
Now we need to call the stored procedure discussed in the previous section. Add a new web form (call it “outputParamDemo”) to an already existing project (as specified in my previous article). Add a button (named “Execute Retrieve OUTPUT parameter”) and a label (named “lblName”).
After designing the form, switch to the code and add the following line at the top.
Imports System.Data.SqlClient
Add the following code to your “Execute Retrieve OUTPUT parameter” button:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim name As String
Dim cn As New SqlConnection("Data Source=.;initial catalog=Northwind;user id=sa")
Dim cmd As New SqlCommand
With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_emp_getEname"
Dim paramName As New SqlParameter
With paramName
.ParameterName = "@name"
.SqlDbType = SqlDbType.VarChar
.Direction = ParameterDirection.Output
.Size = 20
End With
.Parameters.Add("@empno", 1002)
.Parameters.Add(paramName)
.Connection = cn
.Connection.Open()
.ExecuteNonQuery()
name = paramName.Value
'release resources
.Connection.Close()
.Dispose()
End With
Me.lblName.Text = name
End Sub
Set the start page, execute your application (by pressing F5) and click on the button “Execute Retrieve OUTPUT parameter.” Once it executes successfully, you should be able to see the value sent by the stored procedure to your ASP.NET web form.
The most important issue from the above code is that you need to specify “ParameterDirection.Output” as part of the “direction” property. What happens if the stored procedure cannot find any row? It places NULL within the parameter “@name” and sends it back. So, it is our task to check it out in our program.
The code can be further simplified (without creating an explicit parameter object) as follows:
Every stored procedure returns an integer value back to the calling environment (or ADO.NET in this scenario). This happens even if you don’t use a RETURN statement. By default it returns zero (if no RETURN is used).
We can use the RETURN statement to return integer values according to our requirements. In this demonstration, I would like to return a value showing whether an employee exists or not using a RETURN statement. Let us see how it works.
Before going to the ADO.NET code, we need to create a simple stored procedure in SQL Server which returns a value. Using “Query Analyzer,” execute the following script in “Northwind” database.
CREATE PROCEDURE dbo.sp_emp_isEmployeeExist
(
@empno int
)
AS
IF EXISTS (SELECT ename FROM emp WHERE empno =@empno)
RETURN 1
ELSE
RETURN 0
According to the above stored procedure, you can observe that I am sending a value 1 if the employee is found or 0 if no employee is found. It simply accepts “Empno” (“@empno”) as a parameter (which needs to be searched). The stored procedure is named “sp_Emp_isEmployeeExists.” Now, we need to go to ADO.NET to access the same in ASP.NET. The next section accomplishes this.
How to retrieve the value returned by a stored procedure using ADO.NET from ASP.NET: the code
We need to call the stored procedure defined in the previous section. Add a new web form (call it “ReturnValueDemo”) to an already existing project (as specified in my previous article). Add a button named “Receive RETURN value.”
After designing the form, switch to the code and add the following line at the top.
Imports System.Data.SqlClient
Add the following code to your “Receive RETURN value” button:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim name As String
Dim cn As New SqlConnection("Data Source=.;initial catalog=Northwind;user id=sa")
Dim cmd As New SqlCommand
With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_emp_isEmployeeExist"
Dim paramName As New SqlParameter
With paramName
.ParameterName = "@ReturnValue"
.SqlDbType = SqlDbType.Int
.Direction = ParameterDirection.ReturnValue
End With
.Parameters.Add("@empno", 1201)
.Parameters.Add(paramName)
.Connection = cn
.Connection.Open()
.ExecuteNonQuery()
name = IIf(paramName.Value = 0, "Not found", "found")
'release resources
.Connection.Close()
.Dispose()
End With
Me.lblName.Text = name
End Sub
Set the start page, execute your application (by pressing F5) and click on the button “Receive RETURN value.” Once it executes successfully, it returns a value which would be either “found” or “not found.”
The most important issue in the above code is that I used “ParameterDirection.ReturnValue” as part of “Direction” property. You can also see the use of IIF function used to simply notate our own true/false values.
Summary
Even though, I am using only the “SQLCommand” object throughout all of my examples, you can still achieve the same results using “SQLDataReader” or “SQLDataAdapter.” The best and the most efficient approach to working with stored procedures is working with “SQLCommand.” Again, I say, depending on your needs (such as working with data tables) we need to shift to the most efficient way.
I developed the application using Microsoft Windows Server 2003 Standard Edition with Microsoft Visual Studio.NET 2003 Enterprise Architect and Microsoft SQL Server 2000. If anything does not work, please drop me a line so that I can guide you. The entire solution for this article is freely available in the form of a zip downloadable.
In the next article, we shall wok with data tables, data sets, and so on using stored procedures. If possible, we shall also look into stored functions and compare both.
Any comments, suggestions, feedback, bugs, errors, enhancements are highly appreciated at jag_chat@yahoo.com
MS SQL Server 
Working with T-SQL Stored Procedures using ADO.NET
