EXISTS in SQL Server

What is @@IDENTITY in SQL? When we should use and scope of @@IDENTITY?

What is @@IDENTITY in SQL?
The @@IDENTITY is a system function which returns the last inserted identity value.

All the @@IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT are similar functions because all are return the last inserted value into the table’s IDENTITY columns.

The @@IDENTITY and SCOPE_IDENTITY return the current session last identity value but the SCOPE_IDENTITY returns the current scope value.

What is the scope of @@IDENTITY?
In the @@IDENTITY, there are no any limitations for a specific scope.

Syntax: - @@IDENTITY   
Return Type: - numeric (38, 0)

For example as,

-- USE OF @@IDENTITY
INSERT INTO ContactType(Code, Description, IsCurrent, CreatedBy, CreatedOn)  
 VALUES ('IT-PROGRAMING', 'This is a Prrogrammer!', 1, 'Anil Singh', GETDATE());  
GO  
 SELECT @@IDENTITY AS 'COL_IDENTITY';  
GO

The Use of SCOPE_IDENTITY :-

-- DECLARE RETURN TABLE
DECLARE @Return_Table TABLE (Code varchar(10)
 ,Message varchar(100), ID varchar(100))

--INSERT CONTACT TYPE
INSERT INTO ContactType(Code, Description, IsCurrent, CreatedBy, CreatedOn)  
 VALUES ('IT-PROGRAMING', 'This is a Prrogrammer!', 1, 'Anil Singh', GETDATE());  

--USE SCOPE_IDENTITY
IF (@@ERROR <> 0)
BEGIN
 INSERT INTO @Return_Table (Code, Message, ID)
  SELECT 'ERROR', 'An error occurred in updating the customer.', SCOPE_IDENTITY()
 SELECT Code, Message, ID FROM @Return_Table
END
ELSE
BEGIN
 INSERT INTO @Return_Table (Code, Message, ID)
  SELECT 'OK', 'SUCCESS', SCOPE_IDENTITY()
 SELECT Code, Message, ID FROM @Return_Table
END
GO

I hope you are enjoying with this post! Please share with you friends!! Thank you!!!

ANIL SINGH

Hey! I am Anil Singh. I am Active Blogger, Writer and Programmer. I love learning new technologies, programming, blogging and participating the forum discussions more...
http://www.code-sample.com
http://www.code-sample.xyz

You Might Also Like
Post a Comment
www.code-sample.com/. Powered by Blogger.