Hi folks
Hi was creating a simple stored procedure, as i tried to execute that procedure with the data already exist in table(just to check) I am getting following message
(1 row(s) affected)
(1 row(s) affected)
Msg 50000, Level 16, State 1, Procedure insert_loginDetails, Line 30
User Already exists
Msg 266, Level 16, State 2, Procedure insert_loginDetails, Line 2
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
The 'insert_loginDetails' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
Query for the table that i created:
CREATE TABLE [dbo].[user_Login] (
[user_id] INT IDENTITY (1000, 1) NOT NULL,
[user_email] VARCHAR (50) NOT NULL,
[user_password] VARCHAR (25) NOT NULL,
[user_type] VARCHAR (15) NOT NULL,
[user_status] VARCHAR (20) NOT NULL,
[registration_date] DATETIME NOT NULL,
[last_login] DATETIME NOT NULL,
[validation_code] VARCHAR (50) NOT NULL,
[validation_status] VARCHAR (20) NOT NULL,
PRIMARY KEY CLUSTERED ([user_id] ASC),
CONSTRAINT [chk_usertype] CHECK ([user_type]='superadmin' OR [user_type]='admin' OR [user_type]='user'),
CONSTRAINT [chk_userstatus] CHECK ([user_status]='disabled' OR [user_status]='inactive' OR [user_status]='active' OR [user_status]='non validated'),
CONSTRAINT [chk_validationstatus] CHECK ([validation_status]='email not sent' OR [validation_status]='non validated' OR [validation_status]='email sent' OR [validation_status]='validated')
);
Stored Procedure that i created. I am attaching the query for the table and the stored procedure
CREATE PROCEDURE [dbo].[insert_loginDetails]
@user_email varchar(50) , @user_password varchar(25), @user_type varchar(15), @validation_code varchar(40), @user_id int OUTPUT
AS
SET XACT_ABORT on;
BEGIN Transaction T1
DECLARE @user_status varchar(20) = 'non validated'
DECLARE @registration_date datetime = GETDATE()
DECLARE @last_login datetime = GETDATE()
DECLARE @validation_status varchar(20) = 'non validated'
IF NOT Exists (select 1 from user_Login where user_email = @user_email)
BEGIN
insert into user_Login values(@user_email, @user_password, @user_type, @user_status, @registration_date, @last_login, @validation_code, @validation_status)
PRINT('user Details are inserted successfully')
if @@ERROR <> 0
BEGIN
if @@TRANCOUNT> 0
BEGIN
ROLLBACK
RAISERROR('User Login information insertion has failed',16,1)
END
END
END
ELSE
BEGIN
RAISERROR('User Already exists',16,1)
END
if @@ERROR= 0
COMMIT TRANSACTION T1
SELECT @user_id=@@IDENTITY from user_Login
RETURN @user_id
I am not able to figure out whats wrong with my stored procedure