Question

Create 2 stored procedures: sp_AddNewUser and sp_AddNewRole. Use the ManyToMany script as your base table structures.

The stored procedure should accept the parameters needed to input the data for each table.

NOTE: You do not need to input the UserID or RoleID. These are surrogate keys and the system automatically inserts them when you insert a row in the tables.  

On execution, the stored procedure should check the database to see if the user exists, if so, return a message that says so. If the user does not exist, it should insert the information for a new user. Then do the same thing (in a different stored procedure) for the Role.

MANYTOMANY SCRIPT

Create database ManyToMany
-- use master
-- drop database ManyToMany
go
use ManyToMany
go

-- Create tables to be used for the discussion
CREATE TABLE [dbo].[tblUsers](
        [Userid] [int] IDENTITY(100,1) NOT NULL PRIMARY KEY,
        [username] [varchar](32) NOT NULL,
        [password] [varchar](32) NOT NULL,
        [FName] [varchar](32) NULL,
        [LName] [varchar](32) NULL,
        [MName] [varchar](32) NULL,
        [eMail] [varchar](128) NOT NULL,
        [Phone] [varchar](16) NULL
) 

go
CREATE TABLE [dbo].[tblRoles](
        [RoleID] [int] IDENTITY(100,1) NOT NULL PRIMARY KEY,
        [Role] [varchar](32) NOT NULL
)
go
CREATE TABLE [dbo].[tblUserRoles](
        [RoleID] [int] NOT NULL,
        [UserID] [int] NOT NULL
) ON [PRIMARY]

go

-- Add the foreign keys to the userroles table for data integrity

alter table tblUserRoles add constraint FK_Users Foreign Key (UserID) references tblUsers(UserID)
alter table tblUserRoles add constraint FK_Roles Foreign Key (RoleID) references tblRoles(RoleID)
go

-- insert sample data to the tblRoles Table

insert into tblRoles values ('Employee')
insert into tblRoles values ('Manager')
insert into tblRoles values ('Salesman')
insert into tblRoles values ('Security')

go

-- Insert sample data ti the tblUsers table
insert into tblUsers ([username],[password],[FName],[LName],[eMail],[Phone])
  values ('derf', 'password1', 'Fred','Flintstone','[email protected]','555-1234')
insert into tblUsers ([username],[password],[FName],[LName],[eMail],[Phone])
  values ('bam', 'bambam', 'Babbam','Rubble','[email protected]','555-5489')
insert into tblUsers ([username],[password],[FName],[LName],[eMail],[Phone])
  values ('boss', 'bigboss', 'Sylvester','Slate','[email protected]','555-0258')
go

-- this is a script block used to get valid key values for the relationships
-- between the tables and the rows.

begin
declare @user int 
declare @role int
set @user = (select userid from tblUsers where username = 'derf')
set @role = (select RoleID from tblRoles where Role = 'Employee')
insert into tblUserRoles (RoleID,UserID) values(@role,@user)
set @user = (select userid from tblUsers where username = 'bam')
insert into tblUserRoles (RoleID,UserID) values(@role,@user)
set @user = (select userid from tblUsers where username = 'boss')
insert into tblUserRoles (RoleID,UserID) values(@role,@user)
set @user = (select userid from tblUsers where username = 'derf')
set @role = (select RoleID from tblRoles where Role = 'Manager')
insert into tblUserRoles (RoleID,UserID) values(@role,@user)
set @user = (select userid from tblUsers where username = 'boss')
insert into tblUserRoles (RoleID,UserID) values(@role,@user)
set @role = (select RoleID from tblRoles where Role = 'Salesman')
insert into tblUserRoles (RoleID,UserID) values(@role,@user)
end
go

-- see the results

select * from tblUsers
select * from tblRoles
select * from tblUserRoles

Answer 1

CREATE PROCEDURE sp_AddNewUser(
@username as varchar(32) ,
@password as varchar(32),
@FName as varchar(32),
@LName as varchar(32),
@MName as varchar(32),
@eMail as varchar(32),
@Phone as varchar(32)
)
AS
BEGIN
IF not EXISTS (SELECT 1 FROM tblUsers WHERE @username = USERNAME)
BEGIN
INSERT INTO tblUsers
values( @username,@password,@FName,@LName,@MName,@eMail,@Phone )
END
ELSE
BEGIN
RAISERROR('=============================', 0, 1);
RAISERROR('USERNAME ALREDY EXISTS', 0, 1);
RAISERROR('=============================', 0, 1);
END
END
CREATE PROCEDURE sp_AddNewRole(
@Role as varchar(32)
)
AS

BEGIN
IF NOT EXISTS (SELECT 1 FROM tblRoles WHERE Role = @Role)
BEGIN
INSERT INTO tblRoles
values( @Role )
END
ELSE
BEGIN
RAISERROR('=============================', 0, 1);
RAISERROR('USER ROLE ALREDY EXISTS', 0, 1);
RAISERROR('=============================', 0, 1);
END
END