DOCUMENT:Q177187  24-NOV-1997  [vbwin]
TITLE   :BUG: Stored Procedures Cannot Process NULL Parameters from VB
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:5.0
OPER/SYS:WINDOWS
KEYWORDS:vb5all VBKBDB VBKBNet VBKBObj VBKBRDO

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual Basic Enterprise Edition for Windows, version 5.0
---------------------------------------------------------------------

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 Test_Table (Field1) Values (1)
         INSERT 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):

      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=sa;pwd=;" & _
                   "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.

Additional query words: sqlserver
======================================================================
Keywords          : vb5all VBKBDB VBKBNet VBKBObj VBKBRDO
Version           : WINDOWS:5.0
Platform          : WINDOWS
Issue type        : kbbug

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1997.