update querie multiple tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robert Smith - 10 Jun 2008 14:35 GMT

Hi I wish to create a stored procedure that updates multiple tables.
I have a table called User
CREATE TABLE [dbo].[Users](
    [UserId] [int] NULL,
    [Active] [bit] NULL,
    [OrganisationId] [int] NULL,
    [Username] [varchar](20),
    [Password] [varchar](20),
    [Title] [varchar](50) COLLATE,
    [FirstName] [varchar](50) NULL,
    [MiddleNames] [varchar](50),
    [LastName] [varchar](50),
    [Telephone] [varchar](20),
    [Fax] [varchar](20)
) ON [PRIMARY]

Each user has roles that are associated with it in a 1-many relationship
the intermediate table is as follows

CREATE TABLE [dbo].[RoleUsers](
    [RoleUserId] [int] ,
    [RoleId] [smallint],
    [UserId] [int]

How do I create a stored procedure that will update the user and also all
the roles associated with the user.

The generated update user sp is as follows

-- =============================================
ALTER PROC [dbo].[usp_Users_Update]

    @UserId int,
    @Active bit,
    @OrganisationId int,
    @Username varchar(20),
    @Password varchar(20),
    @Title varchar(50),
    @FirstName varchar(50),
    @MiddleNames varchar(50),
    @LastName varchar(50),
    @Telephone varchar(20),
    @Fax varchar(20)
AS
SET NOCOUNT ON
IF @UserId = 0 BEGIN
    INSERT INTO Users (
        [Active],
        [OrganisationId],
        [Username],
        [Password],
        [Title],
        [FirstName],
        [MiddleNames],
        [LastName],
        [Telephone],
        [Fax]
    )
    VALUES (
        @Active,
        @OrganisationId,
        @Username,
        @Password,
        @Title,
        @FirstName,
        @MiddleNames,
        @LastName,
        @Telephone,
        @Fax
    )
    SELECT SCOPE_IDENTITY() As InsertedID
END
ELSE BEGIN
    UPDATE Users SET
        [Active] = @Active,
        [OrganisationId] = @OrganisationId,
        [Username] = @Username,
        [Password] = @Password,
        [Title] = @Title,
        [FirstName] = @FirstName,
        [MiddleNames] = @MiddleNames,
        [LastName] = @LastName,
        [Telephone] = @Telephone,
        [Fax] = @Fax
    WHERE [UserId] = @UserId

END

Thanx in advance

Reply to this Message

Russell Fields - 10 Jun 2008 21:12 GMT

Robert,

The simple answer is: only one table at a time can be updated or inserted.
However, if you pass the appropriate parameters to a stored procedure you
can use those parameters in multiple statements. For example:

IF ... (Users Test)
BEGIN
INSERT INTO Users
INSERT INTO RoleUsers
END
ELSE
BEGIN
UPDATE Users
IF ... (RoleUsers Test)
INSERT INTO RoleUsers
END

In your sample stored procedure below, presumably you would add a Role
parameter that would make it meaningful to update both of these tables.
Also, the above is only a skeleton and may not represent what you actually
want to do.

(I would probably have an UpdateInsertUsers procedure and afterward run an
UpdateInsertUserRoles procedure for however many user roles I wanted to
insert, but that is just my style.)

RLF

> Hi I wish to create a stored procedure that updates multiple tables.
> I have a table called User
[quoted text clipped - 85 lines]
>
> Thanx in advance

Reply to this Message