如何在存储过程中获取主键 [英] How to Get the Primary Key in stored Procedure

问题描述

我一直在用MS SQL编写存储过程.这是下面编写的示例代码.

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

这段代码工作正常.但在某些情况下，它不起作用.
例如:如果表中没有datat并且我已将数据插入该表中，那么它就可以正常工作.再一次，我将相同的数据插入到TABLEA
然后自动不插入数据，但我没有得到TableA的主键.

我需要每次插入都获得TABLEA的主键(如果TABLEA中存在或不存在数据).有没有办法得到它.

请帮帮我，

问候，

Dileep

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

推荐答案

虽然SCOPE_IDENTITY可以很好地获取主键，但我通常更喜欢OUTPUT 条款.主要原因是OUTPUT 子句可以从插入的行中返回更多信息，并且还可以减少往返次数，特别是如果从SQL Server外部调用该语句.
有关更多信息，请参见输出子句 [

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

尝试以下示例

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

这篇关于如何在存储过程中获取主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！