How to Get the Primary Key in stored Procedure

Question

0.00/5 (No votes)

See more:

Hi,

I am Stuck in to write a stored procedure in MS SQL. Here is the Below Sample Code that am wrote.

SQL

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

dilzz

Add a Solution

2 solutions

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[^]

Posted 21-Aug-12 12:01pm

Wendelius

Comments

Prasad_Kulkarni 22-Aug-12 1:54am

Yep +5

Wendelius 22-Aug-12 15:31pm

Thanks :)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

__TR__ · Accepted Answer · 2012-08-20T20:58:00

Try the below sample

SQL

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