update querie multiple tables   

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

date: Tue, 10 Jun 2008 06:35:01 -0700   author:   Robert Smith

Re: update querie multiple tables   

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



"Robert Smith"  wrote in message 
news:1327C31F-6206-44DF-B1DF-1C8F97A949C7@microsoft.com...
> 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
>
>
>

date: Tue, 10 Jun 2008 16:12:27 -0400   author:   Russell Fields