Hello people,
Good Morning!
All right with you ?
In this post, I will demonstrate a script to perform various sweepstakes, as a hidden friend, which is so common among Brazilians at the end of the year. This script was created by the BI analyst, T-SQL expert, Lucas Arrigoni.
The script has some checks, such as validating if the number of participants allows a random draw and it ensures that the person does not take themselves or that two participants take the same person.
To use this script, simply edit the list of participants, fill in their name and email, and run the script. This way, the routine will generate the draw listing and send an email to each participant, containing the name of their secret friend.
Source code:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 | /***************************************************************************************************************************/ /** 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 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', /***************************************************************************************************************************/ /** 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 do not know how to configure or enable Database Mail XP to send emails to SQL Server, see my post. How to Enable, Send, and Monitor Emails by SQL Server (sp_send_dbmail).
Sample Email Sent:
Participante1: Seu amigo secreto é: Participante2
If you prefer to send messages by text message instead of email, see how to send text messages via SQL Server by accessing the post Using the Pushbullet API to send SMS messages in C #, PHP, Java, or SQL Server (with CLR).
That's it folks!
I hope you enjoyed this post and even more.
