Hi,

You fon't have to run select to retrieve the last identity value because it's already stored into system variable @@IDENTITY. See BOL for details. If you need to return value from dynamical SQL take a look at faq #8130.

>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) */

select @req_jeid = @@IDENTITY
>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)