hi everybody,

i am getting error while creating my stored procedures. actually what i want is i want to insert a record into a "jsregistration" table. there is a identity column in "jsregistration" table. after inserting the record, i want retrieve the record that i have inserted and wanto capture the identity column. after catpturing the value into a variable, i would like to insert that value in another table "login" table. i want to do these all things in one stored procedure only. i am trying for that.. but i am getting error

"
Server: Msg 156, Level 15, State 1, Procedure new_user, Line 27
Incorrect syntax near the keyword 'exec'.
"
here i am giving code that have written... please kindly give solution asap

regards
laxminarayana

CREATE PROCEDURE [new_user]
@req_username varchar(50),
@req_FirstName varchar(50),
@req_LastName varchar(50),
@req_Gender varchar(50),
@req_Email varchar(50),
@req_Password varchar(50),
@req_SpecIdentity varchar(50),
@req_SignupDate varchar(50),
@req_LastLogged varchar(50),
@req_AccountDisabled varchar(50),
@req_Terminate varchar(50)
AS

declare @req_DOB1 datetime
/*select @req_DOB1=CONVERT (datetime, @req_DOB)*/
declare @req_signupdate1 datetime
declare @req_lastlogged1 datetime
select @req_signupdate1 = convert (datetime,@req_signupdate)
select @req_lastlogged1 = convert(datetime,@req_lastlogged)

insert into jsregistration (username,firstname,lastname,gender,email) values (@req_username,@req_FirstName,@req_LastName ,@req_Gender ,@req_Email)
declare @sql_stat varchar (100)
declare @req_jeid numeric
select @sql_stat = 'select jeid from jsregistration where username = @req_username'
select @req_jeid = exec(@sql_stat)
insert into login (username,password,specidentity,signupdate,lastlogged,accountdisabled,terminate) values(@req_username,@req_Password,@req_SpecIdentity,@req_SignupDate1,@req_LastLogged1,@req_AccountDisabled,@req_Terminate)