quote:

---

Originally posted by p.shaw3@ukonline.co.uk

I am trying to run the following SP in Query Analyser:-

CREATE PROCEDURE [dbo].[usp_InsertUser]
--CUSTOMER TABLE
@Title Varchar(7), @Account_name Varchar(50), @Account_Contact Varchar(50), @FirstName Varchar(30), @MiddleName Varchar(30),
@Surname Varchar(30), @ProfileType Varchar (10), @EntryDate datetime, @AddressName_No Varchar(30), @Address_Road varchar(30),
@Address_1 Varchar(30), @Address_2 Varchar(30), @Address_County Varchar(30), @Address_Postcode Varchar(10), @Address_Country Char(10), @Telephone1 Varchar(14), @Telephone2 varchar(14), @fax Varchar(14), @Email Varchar(50), @CompanyName Varchar(40), @CompanyAddress Varchar(200),@Spec_Requirements Varchar(100), @Message Varchar(200), @WebEnabled Char(1), @Enabled Char(1), @CreatedBy Varchar(30), @CardAbbr Varchar(15),
--LOGIN TABLE
@CustomerId int, @Cust_Username Varchar(40), @Cust_Password Varchar(30), @Cust_Postcode Varchar(10),
--CC TABLE
@CCName Varchar(30), @EncNo Varchar(100), @CCType Varchar(20), @CCExpMon Char(2), @CCExpYear Char(2), @Valuer Varchar(25),
--OUTPUT VARIABLES
@Duplicate Varchar(30) OUTPUT
AS
If (Select @@ROWCOUNT as 'Rows Affected' from FyldeDB.dbo.Customer_Login
where Cust_Username = @Cust_Username AND Cust_Password = @Cust_Password AND Cust_Postcode = @Cust_Postcode) <> 0

BEGIN
--User exists in DB, send message to page and exit
SET @Duplicate = 'USER EXISTS'
RETURN
END

ELSE

BEGIN
--User Doesn't Exist - DO THE INSERT TO CUSTOMERS
BEGIN TRANSACTION TR_Update
INSERT INTO FyldeDB.dbo.Customer_Details (Title, Account_Name, Account_Contact, FirstName, MiddleName, Surname, ProfileType, EntryDate, AddressName_No, Address_Road,
Address_1, Address_2, Address_County, Address_Postcode, Address_Country, Telephone1, Telephone2, Fax, Email, CompanyName, CompanyAddress, Spec_Requirements,
Message, WebEnabled, Enabled, CreatedBy, CardAbbr)
Values (@Title, @Account_name, @Account_Contact, @FirstName, @MiddleName, @Surname, @ProfileType, @EntryDate, @AddressName_No, @Address_Road, @Address_1, @Address_2, @Address_County, @Address_Postcode, @Address_Country, @Telephone1, @Telephone2, @fax, @Email, @CompanyName, @CompanyAddress, @Spec_Requirements, @Message, @WebEnabled, @Enabled, @CreatedBy, @CardAbbr)
SELECT @@IDENTITY
RETURN @@IDENTITY

--DO THE INSERT TO CUSTOMER LOGIN
INSERT INTO FyldeDB.dbo.Customer_Login (Cust_Username, Cust_Password, CustomerId, Cust_Postcode)
Values (@Cust_Username, @Cust_Password, @@IDENTITY, @Cust_Postcode)
--DO THE INSERT TO CCS

INSERT INTO FyldeDB.dbo.CCS (CCName, EncNo, CCType, CCExpMon, CCExpYear, Valuer, CustomerId)
Values (@CCName, @EncNo, @CCType, @CCExpMon, @CCExpYear, @Valuer, @@IDENTITY)

If @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION TR_Update
SET @Duplicate = 'ERROR OCCURED'
RETURN
END

ELSE

BEGIN
COMMIT TRANSACTION TR_Update
SET @Duplicate = 'UPDATE COMPLETED'

RETURN
END
END
GO

As you hopefully can see, there are 3 inserts to different tables, each one inserting as a batch and only rolling back if an error is encountered. The problem is I keep getting the following error:-

(1 row(s) affected)

(1 row(s) affected)

Server: Msg 266, Level 16, State 2, Procedure usp_InsertUser, Line 36
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
Stored Procedure: FyldeDB.dbo.usp_InsertUser
Return Code = 10002
Output Parameter(s):
@Duplicate = <NULL>
Although the Return Code produces the correct ID, no rows are inserted in any of the 3 tables. Can anyone help please?

Pat.

---

quote:

---

RETURN
Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements following RETURN are not executed.

---

quote:

---

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.

SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

---