Error: 'SubQuery Returned More than 1 Value'

I have some code that calls a stored procedure on SQL Server 2005 using the Microsoft JDBC driver 1.1. The code normally works however, every once in a while an exception is thrown:

Code Snippet

com.microsoft.sqlserver.jdbc.SQLServerException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !, <,<=, >, >=, or when the subquery is used as an expression.

Generally, this has been resolved by restarting SQL Server 2005, but why is it showing up to being with?

Stored Procedure:

Code Snippet

ALTER Procedure [dbo].[addRecord]
@.userID int,
@.itemID int,
@.info varchar(50),
@.comment varchar(50),
@.output int output
AS

Declare @.dateSubmitted datetime
set @.dateSubmitted = getDate();

--Insert the new record. THIS TABLE has an ID identity Primary Key
--column that auto-increments.
insert into RecordTable
(UserPerson, Information, DateSubmitted)
values (@.userID, @.info, @.dateSubmitted);

Get the ID Assigned in the record table. The Item table
has a Foreign key on this column.
Declare @.assignedID int
set @.assignedID = (select ID from RecordTable where DateSubmitted = @.dateSubmitted)

/**Set the output parameter.*/
set @.output = @.assignedID;

--Now update the Item Table.
insert into Item
(ID, RecordID, Comment)
values (@.item,@.assignedID, @.comment);

set @.assignedID = (select ID from RecordTable where DateSubmitted = @.dateSubmitted)

my guess is that you get that error because you insert more than one record with the same datesubmitted value.

|||

CharlieXXX wrote:

set @.assignedID = (select ID from RecordTable where DateSubmitted = @.dateSubmitted)

my guess is that you get that error because you insert more than one record with the same datesubmitted value.

That's not possible. The error occurs even when the RecordTable is empty.
|||

SomeDeveloperPerson wrote:

CharlieXXX wrote:

set @.assignedID = (select ID from RecordTable where DateSubmitted = @.dateSubmitted)

my guess is that you get that error because you insert more than one record with the same datesubmitted value.

That's not possible. The error occurs even when the RecordTable is empty.

Charlie... you're actually correct. I modified the line to:

Code Snippet

set @.assignedID = (select MAX(ID) from RecordTable where DateSubmitted = @.dateSubmitted)

And it works with no problems now. Apparently the code is executing faster than expected so that several records are being written in under a millisecond.
|||

You can reduce the 'effort' of the procedure with this simple alteration:

Instead of having another query to obtain the IDENTITY value of the just entered row, use the SCOPE_IDENTITY() function.

Code Snippet

--Insert the new record. THIS TABLE has an ID identity Primary Key
--column that auto-increments.
insert into RecordTable
(UserPerson, Information, DateSubmitted)
values (@.userID, @.info, @.dateSubmitted);

Declare @.assignedID int
set @.assignedID = SCOPE_IDENTITY()

It saves a small amount of unnecessary server 'work' since the SCOPE_IDENTITY() is part of the return information from the original insert.

And using MAX() in the fashion that you are could potentially have you obtaining the value from a row inserted by another user. Not a very reliable prospect.