Reading Time: 4 minutes
Hello everybody!
Good evening.
In this post I will demonstrate how to create a stored procedure to return a list of valid CPF's or CNPJ's for testing environments and systems. You can capture the result of the procedure and record and a temporary table and test your systems easily now.
If you need a function that validates CPF, CNPJ, Email, Phone and ZIP code, learn more by accessing the post. Validating CPF, CNPJ, Email, Phone, and Zip Code in SQL Server
Procedure for generating CPF and / or CNPJ mass:
View source
Transact-SQL
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 | CREATE PROCEDURE dbo.stpGerador_CPF_CNPJ ( @Quantidade INT = 1, @Fl_Tipo BIT = 1 ) AS BEGIN IF (OBJECT_ID('tempdb..#Tabela_Final') IS NOT NULL) DROP TABLE #Tabela_Final CREATE TABLE #Tabela_Final ( Nr_Documento VARCHAR(18) ) DECLARE @n INT, @n1 INT, @n2 INT, @n3 INT, @n4 INT, @n5 INT, @n6 INT, @n7 INT, @n8 INT, @n9 INT, @n10 INT, @n11 INT, @n12 INT, @d1 INT, @d2 INT -- CPF IF (@Fl_Tipo = 0) BEGIN WHILE (@Quantidade > 0) BEGIN SET @n = 9 SET @n1 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT) SET @n2 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT) SET @n3 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT) SET @n4 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT) SET @n5 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT) SET @n6 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT) SET @n7 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT) SET @n8 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT) SET @n9 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT) SET @d1 = @n9 * 2 + @n8 * 3 + @n7 * 4 + @n6 * 5 + @n5 * 6 + @n4 * 7 + @n3 * 8 + @n2 * 9 + @n1 * 10 SET @d1 = 11 - ( @d1 % 11 ) IF ( @d1 >= 10 ) SET @d1 = 0 SET @d2 = @d1 * 2 + @n9 * 3 + @n8 * 4 + @n7 * 5 + @n6 * 6 + @n5 * 7 + @n4 * 8 + @n3 * 9 + @n2 * 10 + @n1 * 11 SET @d2 = 11 - ( @d2 % 11 ) IF ( @d2 >= 10 ) SET @d2 = 0 INSERT INTO #Tabela_Final SELECT CAST(@n1 AS VARCHAR) + CAST(@n2 AS VARCHAR) + CAST(@n3 AS VARCHAR) + '.' + CAST(@n4 AS VARCHAR) + CAST(@n5 AS VARCHAR) + CAST(@n6 AS VARCHAR) + '.' + CAST(@n7 AS VARCHAR) + CAST(@n8 AS VARCHAR) + CAST(@n9 AS VARCHAR) + '-' + CAST(@d1 AS VARCHAR) + CAST(@d2 AS VARCHAR) SET @Quantidade = @Quantidade - 1 END END -- CNPJ ELSE BEGIN WHILE (@Quantidade > 0) BEGIN SET @n = 9 SET @n1 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT) SET @n2 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT) SET @n3 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT) SET @n4 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT) SET @n5 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT) SET @n6 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT) SET @n7 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT) SET @n8 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT) SET @n9 = 0 SET @n10 = 0 SET @n11 = 0 SET @n12 = 1 SET @d1 = @n12 * 2 + @n11 * 3 + @n10 * 4 + @n9 * 5 + @n8 * 6 + @n7 * 7 + @n6 * 8 + @n5 * 9 + @n4 * 2 + @n3 * 3 + @n2 * 4 + @n1 * 5 SET @d1 = 11 - ( @d1 % 11 ) IF (@d1 >= 10) SET @d1 = 0 SET @d2 = @d1 * 2 + @n12 * 3 + @n11 * 4 + @n10 * 5 + @n9 * 6 + @n8 * 7 + @n7 * 8 + @n6 * 9 + @n5 * 2 + @n4 * 3 + @n3 * 4 + @n2 * 5 + @n1 * 6 SET @d2 = 11 - ( @d2 % 11 ) IF (@d2 >= 10) SET @d2 = 0 INSERT INTO #Tabela_Final SELECT '' + CAST(@n1 AS VARCHAR) + CAST (@n2 AS VARCHAR) + '.' + CAST (@n3 AS VARCHAR) + CAST (@n4 AS VARCHAR) + CAST (@n5 AS VARCHAR) + '.' + CAST (@n6 AS VARCHAR) + CAST (@n7 AS VARCHAR) + CAST (@n8 AS VARCHAR) + '/' + CAST (@n9 AS VARCHAR) + CAST (@n10 AS VARCHAR) + CAST (@n11 AS VARCHAR) + CAST (@n12 AS VARCHAR) + '-' + CAST (@d1 AS VARCHAR) + CAST (@d2 AS VARCHAR); SET @Quantidade = @Quantidade - 1 END END SELECT * FROM #Tabela_Final END |
Thanks guys,
Until the next post!
CPF and CNPJ generator for SQL Server environment testing
CPF and CNPJ generator for SQL Server environment testing
Thanks Dirceu, very useful. I made an adaptation for Postgre that works a little differently. If you do not mind, I will put here as thanks and collaboration with the community:
/ **********************
Generates random CPF and CNPJ numbers
When “type” is:
1: CPF without mask
2: CNPJ Without Mask
3: CPF with Mask
4: CNPJ with Mask
*/
create or replace function cpf_cnpj_genarator (integer type) returns varchar (20)
language plpgsql
as
$$
Decare
n integer;
n1 integer;
n2 integer;
n3 integer;
n4 integer;
n5 integer;
n6 integer;
n7 integer;
n8 integer;
n9 integer;
n10 integer;
n11 integer;
n12 integer;
d1 integer;
d2 integer;
BEGIN
n = 9;
n1 = floor (random () * 10) :: int;
n2 = floor (random () * 10) :: int;
n3 = floor (random () * 10) :: int;
n4 = floor (random () * 10) :: int;
n5 = floor (random () * 10) :: int;
n6 = floor (random () * 10) :: int;
n7 = floor (random () * 10) :: int;
n8 = floor (random () * 10) :: int;
n9 = floor (random () * 10) :: int;
- CPF
IF type in (1,3) then
d1 = n9 * 2 + n8 * 3 + n7 * 4 + n6 * 5 + n5 * 6 + n4 * 7 + n3 * 8 + n2 * 9 * 1
d1 = 11 - (d1% 11);
IF (d1> = 10) then
d1 = 0;
end if;
d2 = d1 * 2 + n9 * 3 + n8 * 4 + n7 * 5 + n6 * 6 + n5 * 7 + n4 * 8 + n3 * 9 * 2 * 10
d2 = 11 - (d2% 11);
IF (d2> = 10) then
d2 = 0;
end if;
return n1 :: varchar ||
n2 :: varchar ||
n3 :: varchar ||
case when tipo = 3 then '.' else ”end ||
n4 :: varchar ||
n5 :: varchar ||
n6 :: varchar ||
case when tipo = 3 then '.' else ”end ||
n7 :: varchar ||
n8 :: varchar ||
n9 :: varchar ||
case when tipo = 3 then '-' else ”end ||
d1 :: varchar ||
d2 :: varchar;
- CNPJ
else
n9 = 0;
n10 = 0;
n11 = 0;
n12 = 1;
d1 = n12 * 2 + n11 * 3 + n10 * 4 + n9 * 5 + n8 * 6 + n7 * 7 + n6 * 8 + n5 * 9 * 4 * X2
d1 = 11 - (d1% 11);
IF (d1> = 10) then
d1 = 0;
end if;
d2 = d1 * 2 + n12 * 3 + n11 * 4 + n10 * 5 + n9 * 6 + n8 * 7 + n7 * 8 + n6 * 9 * 5 + X2 * 4 * n3 * 3;
d2 = 11 - (d2% 11);
IF (d2> = 10) then
d2 = 0;
end if;
return n1 :: varchar ||
n2 :: varchar ||
case when tipo = 4 then '.' else ”end ||
n3 :: varchar ||
n4 :: varchar ||
n5 :: varchar ||
case when tipo = 4 then '.' else ”end ||
n6 :: varchar ||
n7 :: varchar ||
n8 :: varchar ||
case when tipo = 4 then '/' else ”end ||
n9 :: varchar ||
n10 :: varchar ||
n11 :: varchar ||
n12 :: varchar ||
case when tipo = 4 then '-' else ”end ||
d1 :: varchar ||
d2 :: varchar;
end if;
END
$$;
Hi Dirceu, this code was very useful. So far I only saw generators in php, vba and javascript. I will use the snippet on future projects that require sql. Thanks.
I'm using to do my tests. Thank you very much. A doubt. I created a procedure to make inserts in a table. The table with codFunc, Name, Salary and cpf. How can I include your automatically generate valid cpf procedure inside mine? Thanks
Dirceu, good afternoon.
Congratulations on the code, for sure very useful and important.