Microsoft KB Archive/321902: Difference between revisions
m (Text replacement - ">" to ">") |
m (Text replacement - "&" to "&") |
||
Line 130: | Line 130: | ||
<pre class="codesample"> 'Declare and open Connection. | <pre class="codesample"> 'Declare and open Connection. | ||
Dim cn As SqlConnection = _ | Dim cn As SqlConnection = _ | ||
New SqlConnection("Data Source=<server>;" & | New SqlConnection("Data Source=<server>;" & _ | ||
"Initial Catalog=Northwind;" & | "Initial Catalog=Northwind;" & _ | ||
"User ID=<user>;Password=<password>;") | "User ID=<user>;Password=<password>;") | ||
cn.Open() | cn.Open() |
Latest revision as of 14:13, 21 July 2020
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
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.
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
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
- Create a new Visual Basic Console application as follows:
- Start Microsoft Visual Studio .NET.
- On the File menu, point to New, and then click Project.
- Click Visual Basic Projects under Project Types, and then click Console Application under Templates.
Add the following statements at the top of the code window for Module1:
Imports System.Data Imports System.Data.SqlClient
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()
- Modify the SQL Server connection string as necessary for your environment.
- 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.
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
- 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.
Keywords: kbhowtomaster kbsqlclient kbsystemdata KB321902