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

Article translations Article translations
Article ID: 321902 - View products that this article applies to.
This article was previously published under Q321902
Expand all | Collapse all

On This Page

SUMMARY

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

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.

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

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.

Properties

Article ID: 321902 - Last Review: September 4, 2003 - Revision: 2.1
APPLIES TO
  • 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
Keywords: 
kbhowtomaster kbsqlclient kbsystemdata KB321902

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com