CREATE PROCEDURE dbo.sp_Account_I
-- Do NOT prefix your Stored Procedures by "SP_" as this [prefix alone] is very inefficient
	@Id_Package smallint,
	@Amount money,
	@Username varchar(16),
	@Password varchar(88),	-- Why 88 characters? Its a monsterous password!
	@FirstName varchar(32),	-- Why multiples of 2? Users are more likely to expect round-number limits - like 30
	@Surname varchar(32),
	@DateBirth datetime,
	@Email varchar(64), 	-- Too short for real world
	@Id_Country int,
	@Id_Account int OUTPUT,
	@Id_User int OUTPUT
AS

DECLARE @ErrorCode int;
-- ";" not required, remove from all subsequent lines

BEGIN

SET NOCOUNT ON;
-- Move this to the top, immediately under "AS"

SELECT @Id_Account = -1;

BEGIN TRANSACTION

/* Insert the user. */
EXEC @ErrorCode = dbo.sp_User_I1 -- Same comment about the SP_ prefix
-- In order to avoid the parameters being position-dependant, particularly if sp_User_I1 changes, use:
	@Username = @Username, etc.
	@Password, 
	@FirstName, 
	@Surname,
	@DateBirth,
	@Email, 
	@Id_Country,
	@Id_User = @Id_User OUTPUT	-- I think you can only use an output for a named parameter

IF @ErrorCode > 0 AND @@ERROR = 0
BEGIN TRY
	INSERT INTO Accounts(
		Id_Package,
		Id_UserMain,
		Amount)
	VALUES(
		@Id_Package,
		@Id_User,	-- Inconsistent naming, use @Id_UserMain
		@Amount)

	SELECT @Id_Account = SCOPE_IDENTITY();

END TRY
BEGIN CATCH
	SELECT @ErrorCode = 3; /* An unknown error occurred. */
END CATCH;

IF @ErrorCode = 0
	COMMIT TRANSACTION
ELSE
BEGIN
-- Without nested named transactions this will most likely cause the application to fail
-- complaining that the transaction level is unexpected - force an error and see what happens!
	ROLLBACK TRANSACTION
	RETURN @ErrorCode;
END
-- No value is explicitly being returned for the "success" outcome
END

quote:

---

Originally posted by robvolk
2) Instead of using status codes to return error conditions, better to use RAISERROR and return actual errors. ADO and ADO.Net have built-in mechanisms for retrieving these errors from the database, and you don't have to know what the numeric return codes mean. You can generate an error message with the exact text you want.

---