HOW TO: Call Stored Procedures with Optional Values in ADO.NET (321902)

SUMMARY

This step-by-step article demonstrates how to handle optional input parameters for Microsoft SQL Server stored procedures in ADO.NET.

back to the top

Description of the Technique

When you design stored procedures in SQL Server, it is typically good practice to specify a default value for input parameters. For example, if you have a stored procedure that accepts a year parameter and returns the orders that a customer places in the specified year, you can make the current year the default value. If a user does not type a value for the year parameter, the stored procedure still returns reasonable results.

With ADO.NET and the SQL Server .NET Managed Provider, a stored procedure can use the default value for an input parameter in two ways:

• Do not define the SqlParameter object for the parameter that will take its default values. Because the SQL Server .NET Managed Provider uses named parameters, you can define parameters in any order, and you do not have to define all parameters in code.
• Pass Nothing as the value of the parameter if the SqlParameter object is defined and added to the Parameters collection.

back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:

• Microsoft Visual Studio .NET, installed on a compatible Microsoft Windows operating system
• An available instance of Microsoft SQL Server 2000 or Microsoft SQL Server 7.0

This article assumes that you are familiar with the following topics:

• Microsoft Visual Basic .NET programming
• ADO.NET data access
• Parameterized SQL Server stored procedures

back to the top

Create the Sample

1. Start SQL Server Query Analyzer (or SQL Server Enterprise Manager), and then create a new stored procedure in the Northwind sample database as follows:

   USE Northwind
   GO
   
   CREATE PROCEDURE usp_OptionalYear
   @CustID NCHAR(5),
   @Year CHAR(4) = '1997',
   @ShipCountry NVARCHAR(15)
   AS
   IF @Year IS NULL
   	BEGIN
   		SELECT * FROM ORDERS
   		WHERE CustomerID = @CustID
   		AND ShipCountry = @ShipCountry
   		ORDER BY OrderDate
   	END
   ELSE
   	BEGIN
   		SELECT * FROM ORDERS
   		WHERE CustomerID = @CustID
   		AND DATEPART(year, OrderDate) = @Year
   		AND ShipCountry = @ShipCountry
   		ORDER BY OrderDate
   	END
   					

2. Create a new Visual Basic Console application as follows:
   1. Start Microsoft Visual Studio .NET.
   2. On the File menu, point to New, and then click Project.
   3. Click Visual Basic Projects under Project Types, and then click Console Application under Templates.
3. Add the following statements at the top of the code window for Module1:

   Imports System.Data
   Imports System.Data.SqlClient
   					

4. Add the following code to the Sub Main procedure:

           'Declare and open Connection.
           Dim cn As SqlConnection = _
               New SqlConnection("Data Source=<server>;" & _
               "Initial Catalog=Northwind;" & _
               "User ID=<user>;Password=<password>;")
           cn.Open()
           'Declare Command.
           Dim cmd As SqlCommand = _
               New SqlCommand("usp_OptionalYear", cn)
           cmd.CommandType = CommandType.StoredProcedure
           'Declare three Parameters.
           Dim prm As SqlParameter = _
               New SqlParameter("@CustID", SqlDbType.NChar, 5)
           cmd.Parameters.Add(prm)
           prm = New SqlParameter("@Year", SqlDbType.Char, 4)
           cmd.Parameters.Add(prm)
           prm = New SqlParameter("@ShipCountry", SqlDbType.NVarChar, 15)
           cmd.Parameters.Add(prm)
           'Set Parameter values.
           With cmd
               .Parameters("@CustID").Value = "ALFKI"
               .Parameters("@Year").Value = Nothing
               .Parameters("@ShipCountry").Value = "Germany"
           End With
           'Execute stored procedure.
           Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
           Do While dr.Read
               Console.WriteLine("Order ID {0} placed on {1}", dr.GetInt32(0), dr.GetDateTime(3))
           Loop
           'Keep window open to view results.
           Console.ReadLine()
   					

5. Modify the SQL Server connection string as necessary for your environment.
6. Run the project. Notice that the default value of 1997 is used because you passed Nothing for the value of the @Year parameter and did not specify a value for that parameter. The console window displays the three orders that the customer ALFKI placed in 1997.
7. Comment out the following three lines so that the @Year parameter is no longer declared and so that no value is supplied:

           'prm = New SqlParameter("@Year", SqlDbType.Char, 4)
           'cmd.Parameters.Add(prm)
           ...
              '.Parameters("@Year").Value = Nothing
   					

8. Run the project again. Because you did not define the @Year parameter, the default value of 1997 is used again. Additionally, the console window displays the three orders that the customer ALFKI placed in 1997.

back to the top