Help stored procedures

Hello ,

I am in a state of conflict with my c# asp.net web app. Basically i have a stored procedure that updates my customer table with the param supplied, and generates the primary key incrementally. My problem is returning the pk to my asp.net.

Procedure looks like so:

ALTER Procedure newUser (
/* Param List */

@.fname varchar(20),
@.lname varchar(20),
@.address1 varchar(20),
@.address2 varchar(20),
@.city varchar(20),
@.province varchar(20),
@.postalCode varchar(7),
@.country varchar(20),
@.phone varchar(10),
@.email varchar(30),
@.receiveNews bit,
@.archive bit,
@.business varchar(10),
@.fax varchar(10)
)
AS

BEGIN TRANSACTION
INSERT INTO customer (
fname,
lname,
address1,
address2,
city,
province,
postalCode,
country,
phone,
email,
receiveNews,
archive,
business,
fax
)
VALUES (
@.fname,
@.lname,
@.address1,
@.address2,
@.city,
@.province,
@.postalCode,
@.country,
@.phone,
@.email,
@.receiveNews,
@.archive,
@.business,
@.fax
)
COMMIT
RETURN

The syntax has been verified correct and does populate my customers table with valid data.

the column that i wish to return is called "pk_customerId".
I tried using "RETURN pk_customerId" but it gives me an error that the column does not exist even thogh it does.

Any ideas.

Thanks in advancein short, you can do this:

return (scope_identity())

but i'd declare a @.variable and an @.error before your begin tran, then right before your commit do this:

select @.error=@.@.error, @.variable=scope_identity()
if @.error != 0 begin
raiserror ('jkshdk fskjhdf kjshdf jkshdfh', 10, 1)
rollback tran
end
commit
return @.variable