Calling Stored Procedure that does updates/inserts will not be commited on the Database #907
Description
Hi db2rest team,
Tried to call a Stored Procedure on an SQL Server that executes a simple update statement will not be commited on the Database
Did the following on SQL Server
-- Create Users table
CREATE TABLE users (
user_id BIGINT IDENTITY(1,1) PRIMARY KEY,
first_name NVARCHAR(50) NOT NULL,
last_name NVARCHAR(50) NOT NULL,
email NVARCHAR(100) NOT NULL UNIQUE,
phone NVARCHAR(20),
is_active BIT DEFAULT 1,
created_date DATETIME2 DEFAULT GETDATE(),
modified_date DATETIME2 DEFAULT GETDATE()
);
-- Create index on email for faster searches
CREATE INDEX IX_users_email ON users(email);
CREATE INDEX IX_users_is_active ON users(is_active);
-- Insert sample data
INSERT INTO users (first_name, last_name, email, phone) VALUES
('John', 'Doe', '[email protected]', '+1-555-0123'),
('Jane', 'Smith', '[email protected]', '+1-555-0124'),
('Bob', 'Johnson', '[email protected]', '+1-555-0125'),
('Alice', 'Brown', '[email protected]', '+1-555-0126'),
('Charlie', 'Wilson', '[email protected]', '+1-555-0127');
-- Created the Stored Procedure s
CREATE OR ALTER PROCEDURE sp_DeactivateUser
@userid BIGINT,
@RowsAffected INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION
UPDATE users
SET
is_active = 0,
modified_date = GETDATE()
WHERE user_id = @UserId AND is_active = 1;
SET @RowsAffected = @@ROWCOUNT;
commit
END;
GO
CREATE OR ALTER PROCEDURE sp_ActivateUser
@userid BIGINT,
@RowsAffected INT OUTPUT
AS
BEGIN
BEGIN TRANSACTION
SET NOCOUNT ON;
UPDATE users
SET
is_active = 1,
modified_date = GETDATE()
WHERE user_id = @UserId AND is_active = 0;
SET @RowsAffected = @@ROWCOUNT;
commit
END;
GO
--- Calling the Stored Procedures
DECLARE @rowsa int
EXEC sp_DeactivateUser @userid = 5, @RowsAffected = @rowsa output
print @rowsa
DECLARE @rowsa int
EXEC sp_ActivateUser @userid = 5, @RowsAffected = @rowsa output
print @rowsa;
When executing them the user will be deactivated and activate.
However, when calling the SP's via DB2REST the fields will not be commited.
many thanks for your help!