存储过程错误无法解决 [英] Stored procedure Error Cant be resolved

问题描述

嗨大家好

你好我正在创建一个简单的存储过程，因为我试图用表中已存在的数据执行该过程（只是为了检查）我收到以下消息

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

推荐答案

错误1：用户定义错误，所以无需担心..

错误2：尝试 ROLLBACK TRANSACTION T1 而不是 ROLLBACK

错误3：在INSERT，SELECT INTO或批量复制语句之后完成后，@@ IDENTITY包含语句生成的最后一个标识值。如果该语句不影响任何具有标识列的表， @@ IDENTITY将返回NULL 。所以尝试使用 ISNULL（） ..



MSDN建议使用 THROW 而不是 RAISEERROR 使用 XACT_ABORT

http://msdn.microsoft.com/en-us/library/ms188792.aspx [ ^ ]



尝试使用此代码..

Error1: User Defined Error so no need to worry about it..
Error2: Try ROLLBACK TRANSACTION T1 instead of ROLLBACK
Error3: After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement.If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. so try using ISNULL()..

MSDN Suggests using THROW instead of RAISEERROR While Using XACT_ABORT
http://msdn.microsoft.com/en-us/library/ms188792.aspx[^]

Try using this Code..

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
Begin
    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') 
    END
    ELSE
       Throw 50000,'User Already exists',1
 
     if @@ERROR= 0
	COMMIT TRANSACTION T1;
     Else if @@TRANCOUNT <>0
     BEGIN
	ROLLBACK TRANSACTION T1;
	Throw 50000,'User Login information insertion has failed',1
     END

     SELECT @user_id=@@IDENTITY from user_Login -- Try isnull() Here
     RETURN 
End

希望这会有所帮助......

Hope this helps...

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 TRANSACTION T1
   RAISERROR('User Login information insertion has failed',16,1)
   END
  END
    
	

   END
   ELSE
   BEGIN
   ROLLBACK TRANSACTION T1
   RAISERROR('User Already exists',16,1)
   RETURN
   END

if @@ERROR= 0
COMMIT TRANSACTION T1
SELECT @user_id=@@IDENTITY from user_Login
 RETURN ISNULL(@user_id,NULL)

我根据你的建议给我带来了一些变化，这很好用。



如果这是一个好方法，请检查一下吗？



Raja Shekhar先生，你的解决方案有真的证明是有帮助的

I brought some changes as you people suggested, and this works pretty fine.

Please check it, if this is a good way?

Mr. Raja Shekhar, your solution has really proved helpfull

这篇关于存储过程错误无法解决的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！