$$1. Create a stored procure and execution part also needed. Stored Procedure Na

Question

$$1. Create a stored procure and execution part also needed.

Stored Procedure Name usp RegisterUser Brief This procedure is used to insert the details of a customer into the table "tbl_User Description by performing the necessary validations based on the business requirements Input Userld, Parameters UserName, UserPassword, Gender, Emailld, DateOfBirth, Address Output ErrorNumber, Parameters ErrorMessage Functionality. T Check if all the input parameters except address are not null • Check if the User Id contains minimum of 4 and maximum 40 characters • Check if the length of the password contains minimum 8 and maximum 15 characters Check if the gender is 'F' or 'M • Check if the date of birth is less than current date Check if the age of customer is at least 18 years • If all the validations are successful, fetch the RolelD for the role “Customer Insert a record into the table tel_User" with the appropriate values • In case of any exception, return the error number and the error message 1 in case of successful insertion Values • -1 if Userld is null -2 if User Name is null -3 if UserPassword is null -4 if Gender is null -5 if DateofBirth is null -6 if the length of User Id is invalid -7 if the length of password is invalid • -8 if gender is invalid -9 if date of birth is not less than current date Return + 140

Explanation / Answer

Go
CREATE PROCEDURE usp_RegisterUser
(
@UserId VARCHAR(40),
@UserName VARCHAR(50),
@UserPassword VARCHAR(50),
@Gender CHAR(1),
@EmailId VARCHAR(50),
@DateOfBirth DATE,
@Address VARCHAR(200),
@ErrorNumber TINYINT OUTPUT,
@ErrorMessage VARCHAR(20) OUTPUT
)
AS
BEGIN
DECLARE @RoleId TINYINT
DECLARE @AgeOfCustomers INT

BEGIN TRY
   IF(@UserId = 0)
   return -1
   IF(@UserName = 0)
   return -2
   IF(@UserPassword = 0)
   return -3
   IF(@Gender = 0)
   return -4
   IF(@DateOfBirth IS NOT NULL)
   return -5
   IF(LEN(@UserId) > 4 AND LEN(@UserId) < 40)
   return -6
   IF(LEN(@UserPassword) > 8 AND LEN(@UserPassword) <15)
   return -7
   IF(@Gender <> 'F' AND @Gender <> 'M')
   return -8
   IF(@DateOfBirth < GETDATE())
   return -9
   IF(@AgeOfCustomers < 18)
   return -10

   SELECT @RoleId FROM tbl_Role WHERE @RoleId = @RoleId
    
   INSERT INTO tbl_User VALUES(@UserId,@UserName,@UserPassword,@RoleId,@Gender,@EmailId,@DateOfBirth,@Address)
   return 1
END TRY

BEGIN CATCH
   return @ErrorNumber
   return @ErrorMessage

   return -99
END CATCH
END



------   Execution ------

DECLARE @ErrorNumber AS TINYINT
DECLARE @ErrorMessage AS VARCHAR(20)
EXECUTE usp_RegisterUser 1001,'Pawan Kalyan','8912345678','M','User Mail','7-Jul-1992','',
@ErrorNumber output, @ErrorMessage output

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.