Q:

How to return NULL for Identity when no INSERT is performed

I have two tables
CREATE TABLE Temp_1 
( 
ID Int IDENTITY(1,1), 
Name Varchar(500) 
) 

CREATE TABLE Temp_2 
( 
 ID Int IDENTITY(1,1), 
 Name Varchar(500) 
  ) 

  INSERT INTO Temp_1 
  SELECT 'CC' 

  INSERT INTO Temp_2 
  SELECT Name 
  FROM Temp_1 
  WHERE 2 = 1 

There is no insertion into the table Temp_2.So my expected output for the SCOPE_IDENTITY () for the table Temp_2 is NULL.
How can i get the Identity Value of the Temp_2 as null?
If we use the SCOPE_IDENTITY() OR @@IDENTITY, the identity value we are getting is only of the table Temp_1. We need to get the identity value of table Temp_2 which is "null" as there is no insertion in the table Temp_2 .

A:

Neither.  You want to use the output clause:
  DECLARE @ids TABLE (id INT);

  INSERT INTO Temp_1 
      OUTPUT inserted.id INTO @ids
      SELECT 'CC' ;

This is session-safe, statement-safe, multi-threaded safe.  And it allows you to handle multiple row inserts.  And it allows you to return additional columns.
There is no reason to use any other method.

