Tuesday, July 30, 2013

Solving jdbc.SQLServerException: The statement did not return a result set.

My stored procedure (SP) returns the last updated record from a table if totalAmount is not null or zero. The SP runs fine in SQL Server Management Studio.
When I run the same SP from my grails/java app, it throws the error "Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set."

This was because if totalAmount is null or zero, there is nothing to return. Therefore, I added an empty /null return. The code is shown below.

-- Save total_amount of every BU for that day
SELECT @totalAmount = SUM(total_amount) FROM settlement

IF (@totalAmount IS NOT NULL)
 BEGIN
  -- Insert fields in to settlement_batch table 
  INSERT INTO settlement_batch (completed_time_stamp,       
         settlement_batch_number, status_time_stamp, 
         total_amount, updated_by_user_id, created_by_user_id, 
         date_created, last_updated, version)
  SELECT GETDATE(), MAX(ISNULL(settlement_batch_number,0)) + 1, 
         GETDATE(), @totalAmount, @currentUser, @currentUser, 
         GETDATE(), GETDATE(), 0
  FROM settlement_batch
  -- Get the settlement_batch_id of the last inserted record
  SET @new_settlement_id = IDENT_CURRENT('settlement_batch');

  -- Return the last inserted id in settlement_batch
  SELECT ISNULL(@new_settlement_id, 0) AS NewSettlementId
 END
ELSE
 BEGIN
    SELECT NULL AS NewSettlementId
 END