Question

SQL stored procedures

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.

many to many 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

Please see the below querry:

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