Friday, March 30, 2012

ID for New record

Hi,
How can I insert a new record in sql table and retrun the ID of that reocrd using a stored procedure?
Thanks,From Books Online:

INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @.@.IDENTITY AS 'Identity'

In stored proc:

create procedure sp_Test

@.outputvalue int output
as

INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @.outputvalue = @.@.IDENTITY|||Thanks DMWCincy.|||Watch out fot @.@.IDENTITY, better use SCOPE_IDENTITY() besuase @.@.IDENTITY will return the last increment for the table, not necessarily the one that was generated by you transaction.

No comments:

Post a Comment