You are currently offline, waiting for your internet to reconnect

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

This article was previously published under Q321902
For a Microsoft Visual Basic 6.0 version of this article, see 170371.


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


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 NorthwindGOCREATE PROCEDURE usp_OptionalYear@CustID NCHAR(5),@Year CHAR(4) = '1997',@ShipCountry NVARCHAR(15)ASIF @Year IS NULL	BEGIN		SELECT * FROM ORDERS		WHERE CustomerID = @CustID		AND ShipCountry = @ShipCountry		ORDER BY OrderDate	ENDELSE	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.DataImports 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

Article ID: 321902 - Last Review: 09/04/2003 21:56:31 - Revision: 2.1

Microsoft ADO.NET (included with the .NET Framework), Microsoft ADO.NET 1.1, Microsoft Visual Basic .NET 2002 Standard Edition, Microsoft Visual Basic .NET 2003 Standard Edition

  • kbhowtomaster kbsqlclient kbsystemdata KB321902