Hey guys,
Good morning!
Are you all right?

In this post, I will demonstrate a script to carry out different draws, such as hidden friend, which is so common among Brazilians at the end of the year. This script was created by the BI analyst, specialist in T-SQL, Lucas Arrigoni.

The script has some checks, such as validating whether the number of participants allows a random draw and it ensures that the person does not draw themselves or that two participants draw the same person.

To use this script, simply edit the list of participants, filling in the name and email of each one, and run the script. This way, the routine will generate the draw list and send an email to each participant, containing the name of their secret friend.

Source code:

/***************************************************************************************************************************/
/** TABELA DE PARTICIPANTES **/
/***************************************************************************************************************************/
IF(OBJECT_ID('tempdb.dbo.#Participantes') IS NOT NULL) DROP TABLE #Participantes
CREATE TABLE #Participantes (
    Id INT IDENTITY(1,1),
    Nm_Participante VARCHAR(70),
    Nm_Amigo_Secreto VARCHAR(70),
    Ds_Mail VARCHAR(70),
    Id_Rand INT
)

/***************************************************************************************************************************/
/** INSERE OS PARTICIPANTES DO AMIGO SECRETO, ATRIBUINDO UM 
    VALOR RANDOMICO PARA ORDENAR NO MOMENTO DA BUSCA PELO AMIGO SECRETO **/
/***************************************************************************************************************************/
INSERT INTO #Participantes (Nm_Participante, Ds_Mail, Id_Rand) VALUES
('Participante1' , 'Participante1@gmail.com', RAND()*100),
('Participante2' , 'Participante2@gmail.com', RAND()*100),
('Participante3' , 'Participante3@gmail.com', RAND()*100),
('Participante4' , 'Participante4@gmail.com', RAND()*100),
('Participante5' , 'Participante5@gmail.com', RAND()*100),
('Participante6' , 'Participante6@gmail.com', RAND()*100)


IF ((SELECT COUNT(*) FROM #Participantes) < 2)
BEGIN
    RAISERROR('São necessárias ao menos 2 pessoas para participar do sorteio', 16, 1)
    RETURN
END

/***************************************************************************************************************************/
/** VARIÁVEIS PARA O LOOP **/
/***************************************************************************************************************************/
DECLARE @Id SMALLINT = 1,
        @Nm_Amigo_Secreto VARCHAR(70),
        @Mensagem VARCHAR(100),
        @Ds_Mail VARCHAR(70)

/***************************************************************************************************************************/
/** LOOP **/
/***************************************************************************************************************************/
WHILE EXISTS (SELECT TOP 1 NULL FROM #Participantes WHERE Nm_Amigo_Secreto IS NULL)
BEGIN

    /***************************************************************************************************************************/
    /** BUSCA A PESSOA QUE NÃO TEM AMIGO SECRETO PARA DAR CONTINUIDADE A LISTA ENCADEADA **/
    /***************************************************************************************************************************/
    SELECT TOP 1
        @Nm_Amigo_Secreto = A.Nm_Participante
    FROM 
        #Participantes A
    WHERE
        A.Id <> @Id
        AND Nm_Amigo_Secreto IS NULL
        AND NOT EXISTS (SELECT NULL FROM #Participantes B WHERE B.Nm_Amigo_Secreto = A.Nm_Participante)
    ORDER BY 
        A.Id_Rand

    /***************************************************************************************************************************/
    /** FECHAR O CICLO DA LISTA ENCADEADA DO AMIGO SECRETO **/
    /***************************************************************************************************************************/
    IF(@Nm_Amigo_Secreto IS NULL)
    BEGIN

        SELECT TOP 1
            @Nm_Amigo_Secreto = A.Nm_Participante
        FROM 
            #Participantes A
        WHERE
            A.Id <> @Id
            AND NOT EXISTS (SELECT NULL FROM #Participantes B WHERE B.Nm_Amigo_Secreto = A.Nm_Participante)

    END

    /***************************************************************************************************************************/
    /** ATUALIZA O AMIGO SECRETO DO PARTICIPANTE **/
    /***************************************************************************************************************************/
    UPDATE A
    SET A.Nm_Amigo_Secreto = @Nm_Amigo_Secreto
    FROM #Participantes A
    WHERE A.Id = @Id
    
    /***************************************************************************************************************************/
    /** BUSCA OS DADOS PARA ENVIAR O EMAIL **/
    /***************************************************************************************************************************/
    SELECT 
        @Ds_Mail = A.Ds_Mail,
        @Mensagem = A.Nm_Participante + ': Seu amigo secreto é: ' + A.Nm_Amigo_Secreto
    FROM 
        #Participantes A
    WHERE 
        A.Id = @Id

    /***************************************************************************************************************************/
    /** ENVIA EMAIL **/
    /***************************************************************************************************************************/
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'SeuProfileSQLServer',
        @recipients = @Ds_Mail, -- Destinatário
        @subject = 'Amigo Secreto',
        @body = @Mensagem,
        @body_format = 'HTML',
        @from_address = 'remetente@gmail.com' -- Remetente

    /***************************************************************************************************************************/
    /** BUSCA O ID DO AMIGO SECRETO QUE FOI ATRIBUIDO AO PARTICIPANTE, PARA DAR CONTINUIDADE A LISTA ENCADEADA **/
    /***************************************************************************************************************************/
    SELECT TOP 1 
        @Id = Id
    FROM 
        #Participantes
    WHERE 
        Nm_Participante = @Nm_Amigo_Secreto

    /***************************************************************************************************************************/
    SET @Nm_Amigo_Secreto = NULL

END

Example of the result of a draw:

Currently, the routine sends emails to each draw participant, containing the name of their secret friend. If you don't know how to configure or enable Database Mail XP to send emails in SQL Server, see my post How to Enable, Send and Monitor Emails via SQL Server (sp_send_dbmail).

Example of email sent:
Participante1: Seu amigo secreto é: Participante2

If you prefer to send messages by text, instead of email, see how to send text messages via SQL Server by accessing the post How to use the Pushbullet API to send SMS text messages in C#, PHP, Java or SQL Server (with CLR).

That's it, folks!
I hope you enjoyed this post and see you later.