IF EXISTS
(
    SELECT *
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].P_SENHA_SHA1_WEB_CONVENIO')
          AND type IN(N'P', N'PC')
)
    BEGIN
        DROP PROCEDURE [dbo].P_SENHA_SHA1_WEB_CONVENIO;
    END;
GO

CREATE PROCEDURE [dbo].[P_SENHA_SHA1_WEB_CONVENIO] @CGC_CPF   VARCHAR(50),
                                                   @EMAIL     VARCHAR(50),
                                                   @NR_CARTAO VARCHAR(50)
WITH ENCRYPTION
AS
     IF NOT EXISTS
     (
         SELECT TOP 1 SENHA
         FROM [dbo].RC_CLI
         WHERE CGC_CPF LIKE @CGC_CPF
     )
         IF NOT EXISTS
         (
             SELECT TOP 1 SENHA
             FROM [dbo].RC_CLI
             WHERE EMAIL LIKE @EMAIL
         )
             IF NOT EXISTS
             (
                 SELECT TOP 1 SENHA
                 FROM [dbo].RC_CLI_FIDELI_CARTAO
                 WHERE NR_CARTAO LIKE @NR_CARTAO
             ) 
                 --LOGIN NAO ENCONTRADO
                 SELECT SUBSTRING(master.dbo.FN_VARBINTOHEXSTR(HASHBYTES('SHA1', '')), 3, 40) AS SHA1;
             ELSE 
             --SENHA JA CRIPTOGRAFADA MD5 NO BANCO
             SELECT SUBSTRING(master.dbo.FN_VARBINTOHEXSTR
                             (HASHBYTES('SHA1',
                                       (
                                           SELECT TOP 1 SENHA AS SHA1
                                           FROM [dbo].RC_CLI_FIDELI_CARTAO
                                           WHERE NR_CARTAO LIKE @NR_CARTAO
                                       ))
                             ), 3, 40) AS SHA1;;
         ELSE
         --SENHA JA CRIPTOGRAFADA MD5 NO BANCO
         SELECT SUBSTRING(master.dbo.FN_VARBINTOHEXSTR
                         (HASHBYTES('SHA1',
                                   (
                                       SELECT TOP 1 SENHA
                                       FROM [dbo].RC_CLI
                                       WHERE EMAIL LIKE @EMAIL
                                   ))
                         ), 3, 40) AS SHA1;;
     ELSE
     --SENHA JA CRIPTOGRAFADA MD5 NO BANCO
     SELECT SUBSTRING(master.dbo.FN_VARBINTOHEXSTR
                     (HASHBYTES('SHA1',
                               (
                                   SELECT TOP 1 SENHA
                                   FROM [dbo].RC_CLI
                                   WHERE CGC_CPF LIKE @CGC_CPF
                               ))
                     ), 3, 40) AS SHA1;
GO