Stored Procedure Inserting into two tables

[togatown]

I have the follow procedure which works fine when I run it out of the query manager but fils whe run as a stored procedure. When I use it as a procedure, the second table doesn'st populate.

I have a trigger to create the new record in the second table because I have a third party app which needs to create the record but cannot directly access it.

CREATE PROCEDURE kiosk.sp_selfenroll
@firstname nvarchar(30),
@lastname nvarchar(50),
@birthdate varchar (10),
@emailaddress nvarchar(255),
@password nvarchar(8),
@accountnumber nvarchar(20),
@phone nvarchar(30),
@phonecell nvarchar(30),
@phonework nvarchar(30),
@streetaddress nvarchar(50),
@city nvarchar(50),
@st nvarchar(20),
@zip nvarchar(15),
@deladdress nvarchar(50),
@delcity nvarchar(50),
@delstate nvarchar(2),
@delzip int,
@newsletter bit,
@emaildeliver bit,
@specials bit,
@notifications bit,
@firstname2 nvarchar(30),
@lastname2 nvarchar(50),
@birthdate2 varchar (10),
@emailaddress2 nvarchar(255),
@phonecell2 nvarchar(30),
@phonework2 nvarchar(30),
@deladdress2 nvarchar(50),
@delcity2 nvarchar(50),
@delstate2 nvarchar(2),
@delzip2 int,
@newsletter2 bit,
@emaildeliver2 bit,
@specials2 bit,
@notifications2 bit
AS

Insert Into Customer
(FirstName, Lastname, EmailAddress, AccountNumber,
PhoneNumber, Address, City, State, Zip, CustomText1)
Values
(@firstname, @lastname, @emailaddress, @accountnumber,
@phone, @streetaddress, @city, @st, @zip, 'Self Enroll')

Select @@identity

Update CustomerExt Set
Birthdate = @birthdate, Password = @password, PhoneNumberCell = @phonecell,
PhoneNumberWork = @phonework, DeliveryAddress = @deladdress, DeliveryCity = @delcity,
DeliveryState = @delstate, DeliveryZip = @delzip, NewsLetter = @newsletter,
EMailDelivery = @emaildeliver, Specials = @specials, Notifications = @notifications,
FirstName2 = @firstname2, Lastname2 = @lastname2, BirthDate2 = @birthdate2,
EMailAddress2 = @emailaddress2, PhoneNumberCell2 = @phonecell2,
PhoneNumberWork2 = @phonework2, DeliveryAddress2 = @deladdress2,
DeliveryCity2 = @delcity2, DeliveryState2 = @delstate2, DeliveryZip2 = @delzip2,
NewsLetter2 = @newsletter2, EMailDelivery2 = @emaildeliver2,
Specials2 = @specials2, Notifications2 = @notifications2
Where ID = @@identity
GO

Any help appreciated.

Togatown

 

[donutman]

Declare a variable to hold the Identity value. Then use this code to set it.
SET @Ident=@@Scope_Identity
The use of @@Identity is not reliable especially when using a trigger.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[togatown]

donutman,

I feel like kicking myself, I worked too long today. Using this sp through an asp script, didn't set permissions for the second table.........

HAHAHAHAHAHA

Going to get some rest now....

Toga

 

[donutman]

But don't forget to change the @@Identity.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[togatown]

Got it thanks man....