CREATE PROCEDURE dbo.spr_user_insert
	@username varchar(100),
	@password varchar(100)
AS 

DECLARE @ErrorString varchar(255) 
DECLARE @ErrNumber int 
DECLARE @newID int

SET NOCOUNT ON 
BEGIN TRANSACTION 

if not exists(select us_id from users where us_name=@username and us_password=@password)
begin
	insert into users(us_name,us_password) values(@username, @password)
	SELECT @ErrNumber = @@ERROR 
	IF @ErrNumber <> 0 GOTO _error 
	SET @newID = @@IDENTITY
end
else
begin
	SELECT @ErrorString = 'username or password already exist!'
	SELECT @ErrNumber = 1313
	GOTO _error
end

_exit: 
BEGIN 
COMMIT TRANSACTION 
SET NOCOUNT OFF 
SELECT @newID AS success, @ErrNumber AS errornumber, '' as [description] 
RETURN 
END 

_error: 
BEGIN 
ROLLBACK TRANSACTION 
SELECT @ErrorString = description from master..sysmessages where error=@ErrNumber 
SET NOCOUNT OFF 
SELECT -1 AS success, @ErrNumber as errornumber, @ErrorString as [description] 
RETURN 
END


GO