return values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

return values

Author

Topic

darenkov
Yak Posting Veteran

90 Posts

Posted - 2005-01-25 : 09:46:18

I was wondering how I could capture the most recent id from this table after inserting a new record, and to put that id into a variable that will be return this id to my app. I know I can use @@IDENTITY but am having trouble capturing in a variable and using the return command.

Here is my code:

create procedure sp_AddFreeMember

@email_address varchar(55),
@pword varchar(25)

AS

begin transaction Step1

begin
insert into Login(
email_address,
pword
)
select
@email_address,
@pword

if @@error != 0
begin
rollback transaction Step1
return
end

else
begin
commit transaction Step1
end
end

GO

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-01-25 : 09:56:34

And where are you using @@IDENTITY?

[CODE]
create procedure sp_AddFreeMember

@email_address varchar(55),
@pword varchar(25)

AS
declare
@MyErr int,
@NewKey int

begin transaction Step1

begin
insert into Login(
email_address,
pword
) values (
@email_address,
@pword
)

select @MyErr = @@Error, @NewKey = @@IDENTITY -- Consider using Scope_Identity()

if @MyErr != 0
begin
rollback transaction Step1
set @NewKey = 0 -- 0 ==> No New Key available
end
else
begin
commit transaction Step1
end

return @NewKey
end
GO
[/CODE]

HTH

=================================================================

Our elections are free, it's in the results where eventually we pay. -Bill Stern, sports announcer (1907-1971)

darenkov
Yak Posting Veteran

90 Posts

Posted - 2005-01-25 : 10:11:31

thanks for your help.

Would it be difficult to check if the email address already exists prior to inserting the values, and to return an error if it does?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-25 : 10:46:13

[code]
if exists(select * from Login where email_address = @email_address)
begin
RAISERROR ('E-mail already exists')
end
[/code]

then you'd need to handle this error message in the front end.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources