SQL Server存储过程查询 [英] Sql Server Stored procedure Query

问题描述

大家好，

我有一个表T1，其中EMPID列是具有身份标识的主键.

我还有另一个表T2，其中DEPT ID为主键，身份指定，EMPID为外键.

我在T1中有数据，例如empid=24.

现在，我想使用存储过程将数据插入T2.

我希望在使用存储过程将值插入T2时自动插入EMPID值24.

有人可以帮我吗.

谢谢您，

Hi All,

I have a table T1 with column EMPID as primary key with identity specification.

I have another table T2 with DEPT ID as primary key with identity specification and EMPID as Foreign key.

I have data in T1 like empid=24.

Now I want to insert the data into T2 using stored procedures.

I want EMPID value 24 to be inserted automatically while i am inserting values to T2 using stored procedure.

Can anyone help me regarding this.

Thank you,

推荐答案

首先，请遵循以下链接:

First of all, follow this link: Creating Stored Procedures[^]

The below procedure selects employee by his/her FisrtName and LastName. If employee does not exists, adding data into Employee table, then adding data into Employee_Detail table.

CREATE STORED PROCEDURE AddEmpIntoDept
    @LastName NVARCHAR(50), 
    @FirstName NVARCHAR(50),
    @DepId INT
AS 
BEGIN
    DECLARE @empid INT
    DECLARE @retval INT

    SET NOCOUNT ON;
    SELECT @empid = ISNULL(EmpId,0), FirstName, LastName
    FROM Employees
    WHERE FirstName = @FirstName AND LastName = @LastName;

    IF  @empid = 0 
    BEGIN
        INSERT INTO Employess (FirstNam, LastName)
            VALUES (@FirstName, @LastName)
        SET @empid = @@IDENTITY
    END

    INSERT INTO Employees_Detail (EmpId, DeptId)
        VALUES(@DepId, @empid)

    SET @retVal = @@IDENTITY
   
    RETURN @retval
END
GO

插入T1后，使用

@@identity

值将新记录作为外键发送到T2表中

value to send new record into T2 table as foreign key

在2008年使用Scope_Identity可能会更好，但是对于这种特殊情况，这并不重要.

看到这里..

http://msdn.microsoft.com/en-us/library/ms190315.aspx [ ^ ]

might be better using Scope_Identity in 2008 don''t think it''d matter for this particular case though.

see here..

http://msdn.microsoft.com/en-us/library/ms190315.aspx[^]

这篇关于SQL Server存储过程查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！