Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
Hi,
 
I am Stuck in to write a stored procedure in MS SQL. Here is the Below Sample Code that am wrote.
 

	DECLARE @Inserted_Identy Bigint;
	
	if not exists(Select * From TableA 
        Where NAME = @NAME and PINCODE = @Pincode )
    
        Begin    
    
	Insert into TableA (NAME,PINCODE,Address,MOB)
	Values(@NAME,@Pincode,@Address,@MOB)
	
	End		
		
	Set @Inserted_Identy = (SELECT SCOPE_IDENTITY())
	
	
	Update TableB Set TABLEA_ID = @Inserted_Identy
	Where ROOM_ID = @ROOM_ID and BILLNO = @BILLNO
 

This code is working fine. But in certain case it is not working.
For Eg: When there is no datat in the table and i have insert the data into that table then it is working fine. And again i have inserted the same data in to TABLEA
then automatically the data is not inserted but i didnt get the Primary key of TableA.
 
I need to get the Primary key of TABLEA each and every insertion,(if there is data present or not in TABLEA) . Is there any way to get it.
 
PLease Help me,
 
Regards,
 
Dileep
Posted 20-Aug-12 20:50pm
dilzz1.2K
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

While SCOPE_IDENTITY works fine to get the primary key, I typically prefer the OUTPUT clause. The main reason is that the OUTPUT clause can return much more information from the inserted row and also may reduce roundtrips especially if the statement is called from outside the SQL Server.
 
For more information, see OUTPUT Clause[^]
  Permalink  
Comments
Prasad_Kulkarni at 22-Aug-12 1:54am
   
Yep +5
Mika Wendelius at 22-Aug-12 15:31pm
   
Thanks :)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try the below sample
 
DECLARE @Inserted_Identy Bigint;
 
    if not exists(Select * From TableA
        Where NAME = @NAME and PINCODE = @Pincode )
 
    Begin
 
        Insert into TableA (NAME,PINCODE,Address,MOB)
        Values(@NAME,@Pincode,@Address,@MOB)
 
        Set @Inserted_Identy = (SELECT SCOPE_IDENTITY())
 
    End
    ELSE
    BEGIN
        --Here column Id is the primary key column.
        --The below statement will retreive the primary key value where column NAME = @NAME and PINCODE = @Pincode and assign it to variable @Inserted_Identy
        SELECT  @Inserted_Identy = Id from TableA
        Where NAME = @NAME and PINCODE = @Pincode
 
    END
 

 

    Update TableB Set TABLEA_ID = @Inserted_Identy
    Where ROOM_ID = @ROOM_ID and BILLNO = @BILLNO
  Permalink  
Comments
dileepkumarpd at 21-Aug-12 3:08am
   
Thank you Sir, :)
__TR__ at 21-Aug-12 3:11am
   
You are welcome.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web02 | 2.8.140827.1 | Last Updated 21 Aug 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100