ALTER PROCEDURE [dbo].[p_generateLicNbr] 
	-- Add the parameters for the stored procedure here
	@parmAge			nchar(10)	,
	@parmSex			nvarchar (1),
	@parmFirstName		nvarchar(50),
	@parmLicNbr			nvarchar(12) output,
	@parmOK				int			output

AS
BEGIN
	DECLARE @varMaxLicNbr	nvarchar(10)
	SET NOCOUNT ON;
SET @varMaxLicNbr = @parmSex + 
SUBSTRING(@parmAge,9,2) + 
SUBSTRING(@parmAge,6,2) + 
SUBSTRING(@parmAge,3,2) + 
UPPER(SubString(@parmFirstName,1,3));

SET @parmLicNbr = (SELECT MAX(LicNbr) FROM bo_Licence WHERE LicNbr LIKE @varMaxLicNbr + '%');

IF(SUBSTRING(@parmLicNbr, 11,2) < '09') 
	BEGIN 
	SET @parmLicNbr = SUBSTRING(@parmLicNbr, 1, 11) + 
CAST(SUBSTRING(@parmLicNbr, 11, 2) + 1 AS nvarchar(1)) ;
	END
	ELSE IF (SUBSTRING(@parmLicNbr,12, 1) = '9')
		BEGIN
		SET @parmLicNbr = SUBSTRING(@parmLicNbr, 1, 10) + CAST(SUBSTRING(@parmLicNbr, 11, 2) + 1 AS nvarchar(2));
		END
		ELSE
			BEGIN
			SET @parmLicNbr = SUBSTRING(@parmLicNbr, 1, 11) + 
CAST(SUBSTRING(@parmLicNbr, 12, 1) + 1 AS nvarchar(1));
			END
END

SELECT @parmLicNbr AS LicNbr;
	
	IF (@@rowcount > 0)
		BEGIN
			SET @parmOK = 0;
			
		END
	RETURN 0

public LicenceVO generateLicNbr(string sex, DateTime age, string firstName, string licNbr)
        {
            LicenceVO licVO = new LicenceVO();
            DataSet dsRetVal = new DataSet();
            SqlCommand objCmd = new SqlCommand("p_generateLicNbr", connection);
            objCmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter objAdapter = new SqlDataAdapter(objCmd);
            objAdapter.SelectCommand = objCmd;

            objCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
            objCmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;

            objCmd.Parameters.Add("@parmSex", SqlDbType.Char);
            objCmd.Parameters["@parmSex"].Direction = ParameterDirection.Input;
            objCmd.Parameters["@parmSex"].Value = sex;
            objCmd.Parameters["@parmSex"].Size = 1;

            objCmd.Parameters.Add("@parmAge", SqlDbType.DateTime);
            objCmd.Parameters["@parmAge"].Direction = ParameterDirection.Input;
            objCmd.Parameters["@parmAge"].Value = age;
            objCmd.Parameters["@parmAge"].Size = 8;

            objCmd.Parameters.Add("@parmFirstName", SqlDbType.NVarChar);
            objCmd.Parameters["@parmFirstName"].Direction = ParameterDirection.Input;
            objCmd.Parameters["@parmFirstName"].Value = firstName;
            objCmd.Parameters["@parmFirstName"].Size = 30;

            objCmd.Parameters.Add("@parmLicNbr", SqlDbType.NVarChar);
            objCmd.Parameters["@parmLicNbr"].Direction = ParameterDirection.Output;
            objCmd.Parameters["@parmLicNbr"].Value = licNbr;
            objCmd.Parameters["@parmLicNbr"].Size = 12;

            objCmd.Parameters.Add("@parmOK", SqlDbType.Int);
            objCmd.Parameters["@parmOK"].Direction = ParameterDirection.Output;
            objCmd.Parameters["@parmOK"].Size = 10;

            int iRetVal = -100;

            try
            {
                connection.Open();
                objAdapter.Fill(dsRetVal);

                iRetVal = (int)objCmd.Parameters["@RETURN_VALUE"].Value;

                if (iRetVal == 0)
                    foreach (DataRow dr in dsRetVal.Tables[0].Rows)
                    {
                        licVO.LicNbr = Convert.ToString(dr["LicNbr"]).Trim();
                    }
            }
            catch (Exception e)
            {
                string message = e.Message;
            }
            finally
            {
                // Close connection
                if (connection.State != ConnectionState.Closed)
                    connection.Close();
                objCmd.Dispose();
            }
            return licVO;
        }