1 Create a stored procure and execution part also needed Sto

$$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

Solution

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\',\'xyz Ur mail can be used here\',\'7-Jul-1992\',\'\',
@ErrorNumber output, @ErrorMessage output