Working with NULL, OUTPUT and RETURN in ADO.NET
(Page 1 of 4 )
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.
Next: How to work with an OUTPUT parameter of a stored procedure using ADO.NET from ASP.NET: stored procedure >>
More MS SQL Server Articles
More By Jagadish Chaterjee
