Geek Answers Handbook

Getting a value with auto-raising a column from a table, where several are inserted / selected in one stored procedure

I have a stored procedure with several insert / select statements. Let's say I use the first insert to populate the Manager table. Upon insertion, the Manager ID is added (with the addition automatically), but is not specified in the insert statement. Then I want to use the ManagerId from this table to insert a row into another table, where ManagerId is the foreign key. Sample code as follows.

USE [TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sptInsertNewManager]
    -- Add the parameters for the stored procedure here
    @FName varchar(50),
    @LName varchar(50),
    @EMail varchar(100),   
    @UserRoleID int,
    @LANUserID varchar(25), 
    @GroupID int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
INSERT INTO [Manager]           
          ([FName], 
           [LName],
           [Email], 
           [UserRoleID],
           [LANUserID],          
           [ActiveFlag],
           [GroupID]
)
     VALUES
            (@FName
            ,@LName
            ,@EMail
            ,@UserRoleID
            ,@LANUserID
            ,1 
            ,@GroupID);

COMMIT

SELECT ManagerId FROM [Manager] AS newManager WHERE LANUserID = @LANUserID; 
        --also insert into Users table.
    INSERT INTO [dbo].[aspnet_Users] (
        [UserId],
        [UserName],
        [LoweredUserName],
        [ManagerId]
    )
        VALUES (
            NEWID(),
            @LANUserID,
            LOWER(@LANUserID),
            newManager)
END

This obviously does not work. It was my attempt to solve this. I am new to SQL, so any help with this problem would be greatly appreciated.

+5
source share
6

scope_identity() , :

DECLARE @ID     int


INSERT ......
SELECT @ID=scope_identity()

@ID ,

note: SCOPE_IDENTITY() @@IDENTITY, Identity , , ( ).

, ( ), OUTPUT INTO:

declare @test table (RowID  int identity(1,1) primary key not null, RowValue varchar(10) null)
declare @OutputTable table (RowID int not null)

insert into @test (RowValue)
    OUTPUT INSERTED.RowID
    INTO @OutputTable
    SELECT 'A'
    UNION SELECT 'B'
    UNION SELECT 'C'
    UNION SELECT 'D'
    UNION SELECT 'E'


select * from @OutputTable

:

(5 row(s) affected)
RowID
-----------
1
2
3
4
5

(5 row(s) affected)
+7

MS Sql

, , (Identity Column in ms-sql parlance), :

@id = SCOPE_IDENTITY()

, , , ( scope_identity())

+2

Yuck... ...

, ( COMMIT ). BEGIN TRANSACTION COMMIT INSERT SELECT, .

:

CREATE PROCEDURE [dbo].[sptInsertNewManager]
    -- Add the parameters for the stored procedure here
    @FName varchar(50),
    @LName varchar(50),
    @EMail varchar(100),   
    @UserRoleID int,
    @LANUserID varchar(25), 
    @GroupID int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

DECLARE @ManagerID  INT

BEGIN TRANSACTION    

    -- Insert statements for procedure here
INSERT INTO [Manager]           
          ([FName], 
           [LName],
           [Email],     
           [UserRoleID],
           [LANUserID],          
           [ActiveFlag],
           [GroupID]
)
     VALUES
                (@FName
                ,@LName
                ,@EMail
            ,@UserRoleID
            ,@LANUserID
                ,1 
            ,@GroupID);

-- Collect the ID you just created
SELECT @ManagerID = SCOPE_IDENTITY()


        --also insert into Users table.
    INSERT INTO [dbo].[aspnet_Users] (
        [UserId],
        [UserName],
        [LoweredUserName],
        [ManagerId]
    )
        VALUES (
                NEWID(),
                @LANUserID,
                LOWER(@LANUserID),
                @ManagerID)   -- This is the new identity you just created

COMMIT

END
+2

, , , . .

+1

, :

SELECT @@identity as 'Identity'

, :

int ID = Convert.ToInt32(cmdinsert.ExecuteScalar());
+1

@@Identity

SELECT @ManagerId=@@Identity

Scope_Identity IDENT_Current

0

More articles:


All Articles