Microsoft KB Archive/321902

Knowledge Base

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

Article ID: 321902

Article Last Modified on 9/4/2003

---

APPLIES TO

• Microsoft .NET Framework 1.1 Service Pack 1
• Microsoft ADO.NET 1.1
• Microsoft Visual Basic .NET 2002 Standard Edition
• Microsoft Visual Basic .NET 2003 Standard Edition

---

This article was previously published under Q321902

For a Microsoft Visual Basic 6.0 version of this article, see 170371.

IN THIS TASK

SUMMARY

• Description of the Technique
• Requirements
• Create the Sample

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

Keywords: kbhowtomaster kbsqlclient kbsystemdata KB321902

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.