CODEWITHSUNDEEP
sqlstored-proceduresinsert
gallie
gallie

Reputation: 143

How do I write a stored procedure that requires an id to be created and then used later in the same procedure?

I am trying to write a stored procedure that writes the details of a new user (NonMembers table) to a data base and then uses the auto generated id for that user in the next part of the code to register that user to a competition (registration table). My attempt at doing this is as follows but clearly my SQL skills are a long way from where they need to be?

All help would be greatly appreciated.

ALTER PROCEDURE [dbo].[RegisterNonMember]
    @CompetitionId INTEGER,
    @IsMember BIT,
    @FirstName  NVARCHAR(50),
    @Surname  NVARCHAR(50),
    @Handicap INTEGER,
    @Club NVARCHAR(50),
    @CountyId INTEGER,
    @CountryId INTEGER,
    @PlayersStartTime TIME

AS
BEGIN

DECLARE @NonMember TABLE
    (NonMemberId INTEGER,
    FirstName  NVARCHAR(50),
    Surname  NVARCHAR(50),
    Handicap INTEGER,
    Club NVARCHAR(50),
    CountyId INTEGER,
    CountryId INTEGER
) 
INSERT INTO [dbo].[NonMembers]
           (
           FirstName
           ,Surname
           ,[Handicap]
           ,[Club]
           ,CountyId
           ,CountryId       
)
     VALUES
           (@FirstName
           ,@Surname
           ,@Handicap
           ,@Club
           ,@CountyId
           ,@CountryId
           )

--UPDATE    @NonMember
--SET       [@NonMember].NonMemberId = [dbo].[NonMembers].[NonMemberId]
--FROM  [dbo].[NonMembers]
--JOIN  @NonMember ON [@NonMember].NonMemberId = [dbo].[NonMembers].[NonMemberId]
--WHERE @NonMember.FirstName = [dbo].[NonMembers].[FirstName]
--AND       @NonMember.Surname = [dbo].[NonMembers].[Surname]
--AND       @NonMember.Handicap = [dbo].[NonMembers].[Handicap]
--AND       @NonMember.Club = [dbo].[NonMembers].[Club]

DECLARE @Registration TABLE
    (CompetitionId INTEGER,
    IsMember BIT,
    NonMemberId INTEGER,
    PlayersStartTime TIME
    )

INSERT INTO [dbo].[Registration]
            (
            [CompetitionId]
            ,[IsMember]
            ,[NonMemberId]
            ,[PlayersStartTime])
    VALUES
            (@CompetitionId
            ,@IsMember
            ,@NonMemberId
            ,@PlayersStartTime)  
END

Upvotes: 0

Views: 98

Answers (1)

Barry Kaye
Barry Kaye

Reputation: 7761

After your INSERT add:

DECLARE @UserID INT
SELECT @UserID = SCOPE_IDENTITY()

Upvotes: 3

Related Questions