BUG: Stored Procedure May Fail with Error 264

• Microsoft SQL Server, versions 6.0 and 6.5

SYMPTOMS

When you run a stored procedure with two parameters that performs an update, you may receive the following error message if the datatype of the first field in the target table is defined as smallint:

   Msg 264, Level 16, State 1
   Column name  appears more than once in the result column list.

• The first field's datatype of the insertion target table is defined as smallint.
• The stored procedure takes only two parameters.
• Those two input parameters are in the particular order as in the following demonstration scenario:

        CREATE TABLE NextIDs
        (
           SmallIntID smallint,
           NextID int
        )
        GO
  
        INSERT NextIDs values (0, 0)
        GO
  
        DROP PROCEDURE sp_GetNextID
        GO
  
        CREATE PROCEDURE sp_GetNextID
           (@IDName varchar(15),
           @NextID int OUTPUT)
        AS
           UPDATE NextIDs SET NextID = NextID + 1, @NextID = NextID
        GO
  
        DECLARE @NextID int
        EXEC sp_GetNextID 'TEST', @NextID OUTPUT
        SELECT @NextID
  
  

WORKAROUND

To work around this problem, do any of the following:

• Swap the two input parameters of the stored procedure in the creation and calling of scripts.
• Redefine the table so that the first field's datatype is not smallint.
• Redefine the stored procedure so that it takes more or fewer parameters.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.