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."
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
