I needed to
create a SQL procedure to automate adding a new user to a user table, and then
also adding a relationship for this new user to a department. When I add a new
user, SQL creates the user’s unique id (UserID). I need this new id to create
the department relationship, but how do I get it (all in one procedure)?
I made
use of the OUTPUT option of the INSERT command as follows:
SQL Stored Procedure (SQL 2008 r2)
|
-- =============================================
-- Author: <Carl Neer>
-- Create date: <11/20/2013>
-- Description: <Creates a new user and links them to
a department.>
--
=============================================
CREATE PROCEDURE
[dbo].[pr_AddUser_yourdbname]
-- Define the passed parameters
@UserName
varchar(40),
@FirstName
varchar(20),
@LastName
varchar(20),
@Department
varchar(100)
AS
BEGIN
SET NOCOUNT ON;
-- Define a table and int variable to hold the
assigned ID
DECLARE @TempTable TABLE (UserID int);
DECLARE @UserID int;
-- Insert the new user
INSERT
INTO User
(
UserName,
FirstName,
LastName,
IsActive
)
OUTPUT –- *** Output
the inserted ID to our table ***
inserted.UserID
INTO
@TempTable
VALUES
(
@UserName,
@FirstName,
@LastName,
'TRUE'
);
-- Get the new user ID into our int variable
SET @UserID =
(SELECT UserID FROM @TempTable);
-- Get Department ID for the passed in
department name
DECLARE
@DepartmentID int =
(SELECT DepartmentID
FROM
Department
WHERE Department = @Department);
-- Insert into our department relationship table,
using the
-- new UserID just created and the department id from above
INSERT
INTO User_Department
(
UserID,
DepartmentID
)
VALUES
(
@UserID,
@DepartmentID
);
END
GO
|
No comments:
Post a Comment