BUG: Stored Procedures Cannot Process NULL Parameters from VB (177187)


The information in this article applies to:

  • Microsoft Visual Basic Enterprise Edition for Windows 5.0
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
This article was previously published under Q177187

SYMPTOMS

A SQL stored procedure produces unexpected results if a NULL parameter is passed from a Visual Basic application using RDO. This behavior also occurs if the SQL stored procedure declares an optional parameter whose default value is NULL and no parameters are passed.

RESOLUTION

The workaround is to modify the SELECT statement in the stored procedure.

If a stored procedure is declared as: 
   CREATE PROC Test_SP
      @Param VARCHAR (20) = NULL
   AS
   SELECT * FROM Test_Table WHERE Field = @Param
   GO

replace it with: 
   CREATE PROC Test_SP
   @Param VARCHAR (20) = NULL
   AS
   IF @Param = NULL
      SELECT * FROM Test_Table WHERE Field = NULL
   ELSE
      SELECT * FROM Test_Table WHERE Field = @Param
   GO

The following can also be used: 
   CREATE PROC Test_SP
   @Param VARCHAR (20) = NULL
   AS
   SELECT * FROM Test_Table WHERE (Field = NULL and @Param = NULL) OR
                                 (Field = @Param)
   GO

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. We are researching this bug and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Run the following SQL script on the SQL Server: 
          CREATE PROC Test_SP

            @Param VARCHAR (20) = NULL

         AS

         SELECT * FROM Test_Table WHERE Field2 = @Param

         GO

        CREATE TABLE Test_Table
         (
            Field1 INT NULL,
            Field2 VARCHAR (20) NULL
         )
         GO

         INSERT INTO Test_Table (Field1) Values (1)
         INSERT INTO Test_Table (Field1) Values (2)
  2. You should now have a stored procedure called Test_SP and a table called Test_Table that contains the following data: 
       Field1      Field2
   ----------- --------------------
   1           (null)
   2           (null)
  3. From ISQL or SQL Query Tool, type "Test_SP" and note that two rows are returned.
  4. Start Visual Basic, add a CommandButton, and place the following RDO code in the Click event (Note The Microsoft Remote Data Object must be added from Project References menu).

    Note You must change UID <username> and PWD <strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.
          Dim cn                   As New rdoConnection
      Dim rs                   As rdoResultset
      Dim sql                  As String

      cn.CursorDriver = rdUseOdbc
      cn.Connect = "driver={sql server};" & _
                   "server=your_server_name;" & _
                   "uid=<username>;pwd=<strong password>;" & _
                   "database=pubs;"
      cn.EstablishConnection rdDriverNoPrompt

      sql = "Test_SP"
      Set rs = cn.OpenResultset(sql, rdOpenKeyset, rdConcurReadOnly)

      If Not rs.EOF Then rs.MoveLast
      MsgBox rs.RowCount

      rs.Close
      cn.Close

      Set rs = Nothing
      Set cn = Nothing
  5. Note that no rows are returned.
Modification Type:MajorLast Reviewed:11/5/2003
Keywords:kbbug KB177187
©2003 Microsoft Corporation. All rights reserved.