Hi guys,

I'm coming from SQL Server 2000. I'm trying to create a stored
procedure with a manual transaction. However, I always get the error
"General Error" which is not the most friendly error message Any
help would be greatly appreciated! Thanks!

CREATE PROCEDURE InsertBusiness
(
OUT :CustomerID IDENTITY,
IN :BusinessName CHAR(35),
IN :ContactName CHAR(35),
IN :AddressLine1 CHAR(35),
IN :AddressLine2 CHAR(35),
IN :City CHAR(20),
IN :ZipCode CHAR(10),
IN :BusPhoneNumber CHAR(20),
IN :HomePhoneNumber CHAR(20),
IN :CellPhoneNumber CHAR(20),
IN :EmailAddress CHAR(40)
)
AS
BEGIN
START TRANSACTION
-- Insert new identity
INSERT INTO CustomerIdentity
VALUES(0, :BusinessName);

SET :CustomerID = @@IDENTITY;

IF SQLSTATE <> 00000 THEN ROLLBACK;
ELSE
BEGIN

INSERT INTO BusinessCustomer
(
CustomerID,
BusinessName,
ContactName.
AddressLine1,
AddressLine2,
City,
ZipCode,
State,
BusPhoneNumber,
HomePhoneNumber,
CellPhoneNumber,
EmailAddress,
CustomerTypeID,
FinancialCompanyID,
CreditLimit,
CreditTerms,
DeliveryID,
DiscountPercentage,
TotalAmountDue,
BalanceOwing,
LastPaymentAmount,
AllowPartialShipment,
NameIsReversed,
Tax,
DefaultPriceID,
CollectorID,
NoMailWanted,
NoStatementWanted,
HasAlert,
Alert,
cust_slm,
cust_ins,
cust_fc_flg,
cust_prom_dat,
cust_status_dat,
cust_aged_ranking,
cust_last_contr,
cust_filler_3,
cust_equi_rpt_flg,
cust_equi_assoc
)
VALUES
(
:CustomerID,
:BusinessName,
:ContactName,
:AddressLine1,
:AddressLine2,
:City,
:ZipCode,
:State,
:BusPhoneNumber,
:HomePhoneNumber,
:CellPhoneNumber,
:EmailAddress,
'OC',
0,
0,
3,
1,
0,
0.00,
0.00,
0.00,
1,
0,
'G&P',
2,
027,
0,
0,
1,
' ',
032,
1,
1,
0,
0,
0,
0,
0,
0,
0
);

IF SQLSTATE <> 00000 THEN ROLLBACK ;
ELSE COMMIT;
END IF

END
END IF
END

Just a guess, but I think it might be as simple as adding a few more
semicolons by the END statements.

To debug this, simply strip out the contents of the SP, then add them
back in one piece at a time. By the way, I think you need the
keyphrase "WITH DEFAULT HANDLER" if you plan on handling the SQLSTATE
messages internally inside the procedure.
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive.SQL Service & Support - March, 2006 ***
*** Chicago: Pervasive DataExchange Class - March, 2006 ***

Cyphos wrote: