Bases de Datos - ayuda con estos ejercicios porfavor

CREATE FUNCTION [dbo].[Retornar_Codigo](

     @p_Codigo AS CHAR(10))

RETURNS  CHAR(10)

AS

BEGIN

     DECLARE @Valor_Retorno AS CHAR(10)

     DECLARE @Valor_Intermedio AS VARCHAR(6)

     DECLARE @Valor_Esperado as varchar(10)

     DECLARE @Maximo as int

SELECT @Valor_Intermedio = CONVERT(varchar(4), YEAR(DATE)) + SUBSTRING(C.FirstName,1,1)

FROM Rental R

INNER JOIN Client C ON C.Client_Key = R.Client_Key

WHERE C.Client_Key = @p_Codigo

SET @Maximo = (SELECT (MAX(CLIENT_KEY) +1) FROM CLIENT)

SET @Valor_Esperado = (SELECT RIGHT('0000' + @Maximo, 5))

SET @Valor_Retorno =  @Valor_Intermedio + @Valor_Esperado

     RETURN @Valor_Retorno

 END

CREATE PROCEDURE SPR_D_Registro

@p_CONDICION  	INT,

@p_RESULTADO    INT OUT

AS

    DECLARE @DatoExiste INT

    SET @DatoExiste = 0

    SET @p_RESULTADO = 0

BEGIN TRY

       SET @DatoExiste = (SELECT TOP 1 CLIENT_KEY FROM CLIENT WHERE CLIENT_KEY = @p_CONDICION)

			 IF (@DatoExiste <> 0)

				 BEGIN

					  SET @p_RESULTADO = 1

				 END

			 ELSE

				 BEGIN

					  DELETE FROM CLIENT WHERE CLIENT_KEY = @p_CONDICION

				 END

 END TRY

BEGIN CATCH

      SET @P_RESULTADO = ERROR_NUMBER()

      RETURN @P_RESULTADO

END CATCH

CREATE FUNCTION [dbo].[Retornar_Codigo](

@p_Codigo AS CHAR(10))

RETURNS CHAR(10)

AS

BEGIN

DECLARE @Valor_Retorno AS CHAR(10)

DECLARE @Valor_Intermedio AS VARCHAR(6)

DECLARE @Valor_Esperado as varchar(10)

DECLARE @Maximo as int

SELECT @Valor_Intermedio = CONVERT(varchar(4), YEAR(DATE)) + SUBSTRING(C.FirstName,1,1)

FROM Rental R

INNER JOIN Client C ON C.ClientKey = R.ClientKey

WHERE C.ClientKey = @p_Codigo

SET @Maximo = (SELECT (MAX(CLIENTKEY) +1) FROM CLIENT)

SET @Valor_Esperado = (SELECT RIGHT('0000' + @Maximo, 5))

SET @Valor_Retorno = @Valor_Intermedio + @Valor_Esperado

RETURN @Valor_Retorno

END

CREATE PROCEDURE SPR_D_Registro

@p_CONDICION INT,

@p_RESULTADO INT OUT

AS

DECLARE @DatoExiste INT

SET @DatoExiste = 0

SET @p_RESULTADO = 0

BEGIN TRY

SET @DatoExiste = (SELECT TOP 1 CLIENTKEY FROM RENTAL WHERE CLIENT_KEY = @p_CONDICION)

IF (@DatoExiste <> 0)

BEGIN

SET @p_RESULTADO = 1

END

ELSE

BEGIN

DELETE FROM CLIENT WHERE CLIENTKEY = @p_CONDICION

END

END TRY

BEGIN CATCH

SET @P_RESULTADO = ERROR_NUMBER()

RETURN @P_RESULTADO

END CATCH