1 Create a stored procure and execution part also neededSolu

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

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\',\'user mail\',\'7-Jul-1992\',\'\',
@ErrorNumber output, @ErrorMessage output