IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usf_calcula_dv_nfe]') AND type IN (N'FN') ) DROP FUNCTION usf_calcula_dv_nfe; GO CREATE FUNCTION usf_calcula_dv_nfe(@number varchar(max)) RETURNS bit -- Nome Artefato/Programa..: usf_calcula_dv_nfe.sql -- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) -- Data Inicio ............: 29/03/2012 -- Data Atualizacao........: 29/03/2012 -- Versao..................: 0.01 -- Compilador/Interpretador: T-SQL (Transact SQL) -- Sistemas Operacionais...: Windows -- SGBD....................: MS SQL Server 2005/2008 -- Kernel..................: Nao informado! -- Finalidade..............: Store Procedure (Function) que calcula o digito verificador da chave de acesso NFe usando o modulo 11 -- OBS.....................: A entrada é um varchar e o retorno é um bit 1 para valido e 0 para invalido -- ........................: -- AS BEGIN DECLARE @i int, @ix int, @c int, @keyx int, @numberx varchar(44), @r int SET @c = 2 SET @i = 1 SET @ix = 43 SET @keyx = 0 -- Pre-validacao 1, se e nulo, entao retorna nulo IF @number IS NULL BEGIN SET @r = NULL RETURN (@r) END --fim_se -- Pre-validacao 2, se e maior que 44 digitos , entao retorna 0 IF LEN(@number) < 44 BEGIN SET @r = 0 RETURN (@r) END --fim_se -- Pre-validacao 3, se e tem alguma letra, entao retorna 0 IF (SELECT CASE WHEN patindex('%[^0-9]%', @number) > 0 THEN 1 ELSE 0 END) = 1 BEGIN SET @r = 0 RETURN (@r) END --fim_se -- Pre-validacao 4, se e menor que 44 digitos , pode ser oriundo de bigint, então fazer tratamento de zeros SET @numberx = @number IF LEN(@number) < 44 BEGIN SET @numberx = REPLICATE('0',44-LEN(@number))+@number END --fim se -- Loop por cada numero mutiplicando-o pelos valores de @c -- Simulando o for invertido com passo -1 WHILE (@i <= (len(@numberx)-1)) BEGIN --Verifica se o valor de @c for maior que nove, entao passa o valor pra 2 IF @c > 9 BEGIN SET @c = 2 END --fim_se --Soma os valores mutiplicados SET @keyx = @keyx + (convert(int,substring(@number,@ix,1)) * @c) --Controle de contadores SET @c = @c + 1 SET @i = @i + 1 SET @ix = @ix - 1 END --fim_enquanto --Obtem o digito verificador IF ((@keyx % 11) = 0 OR (@keyx % 11) = 1) BEGIN SET @r = 0 END ELSE BEGIN --senao SET @r = 11 - (@keyx % 11) END --fim_se IF @r = convert(int,substring(@numberx,len(@numberx),1)) BEGIN SET @r = 1 END ELSE BEGIN --senao SET @r = 0 END --fim_se RETURN (@r) END; GO -- Chamada da function SELECT dbo.usf_calcula_dv_nfe('24110509540525000194550010000007091242050760'); SELECT dbo.usf_calcula_dv_nfe('24110509540525000194550010000007071681710981'); -- Habilitando no campo da CHAVE de NFE (chave_nfe) /* ALTER TABLE nota_fiscal_eletronica ADD CONSTRAINT ck_nota_fiscal_eletronica_usf_calcula_dv_nfe CHECK (dbo.usf_calcula_dv_nfe(chave_nfe)=1); */ GO
DBA, MySql, Oracle, Postgres, SQL Server, DB2, BI, TI, Business Intelligence, Data Science, Machine Learning, IoT, Arduino
quinta-feira, 29 de março de 2012
Store Function SQL Server para validar CHAVE de NFE
Store Function SQL Server para validar CHAVE de NFE
Store function para validar CHAVE de NFE em SQL do MSSQL SERVER 2005 e 2008.
Essa function serve para validar Nota Fiscal Eletrônica, a entrada de parametro é um varchar
retorna 1 para CHAVE de NFE válida e 0 para CHAVE de NFE inválida e NULL quanto for NULO.
Segue a function....
terça-feira, 27 de março de 2012
Formatar CPF em SQL
Store function para formatar CPF em SQL Server
IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usf_formata_cpf]') AND type IN (N'FN') ) DROP FUNCTION dbo.usf_formata_cpf; GO CREATE FUNCTION dbo.usf_formata_cpf(@cpf varchar(max), @mascara bit) RETURNS varchar(max) AS -- Nome Artefato/Programa..: usf_formata_cpf.sql -- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) -- Data Inicio ............: 21/02/2012 -- Data Atualizacao........: 21/02/2012 -- Versao..................: 0.01 -- Compilador/Interpretador: T-SQL (Transact SQL) -- Sistemas Operacionais...: Windows -- SGBD....................: MS SQL Server 2005/2008 -- Kernel..................: Nao informado! -- Finalidade..............: Store Procedure (Function) para mascara o numero do CPF -- OBS.....................: A entrada é um varchar e o retorno é um varchar formatado do cpf, parametro mascara setado em 0 apenas string com zeros, 1 formata o cpf de fato -- ........................: -- BEGIN DECLARE @cpf_temp varchar(max) -- Pre-validacao 1, se e nulo, entao retorna nulo IF @cpf IS NULL BEGIN RETURN (@cpf) END --fim_se -- Pre-validacao 2, se e diferente de 11 digitos , entao retorna 0 IF LEN(@cpf) > 11 BEGIN RETURN (@cpf) END --fim_se -- Pre-validacao 3, se e tem alguma letra no cpf, entao retorna 0 IF (SELECT CASE WHEN patindex('%[^0-9]%', @cpf) > 0 THEN 1 ELSE 0 END) = 1 BEGIN RETURN (@cpf) END --fim_se -- Pre-validacao 4, se e menor que 11 dig, pode ser oriundo de um bigint, entao colocar zeros a frente SET @cpf_temp = @cpf IF LEN(@cpf) < 11 BEGIN SET @cpf_temp = REPLICATE('0',11-LEN(@cpf))+@cpf END --fim_se -- Se e para formatar mesmo IF @mascara = 1 BEGIN SET @cpf_temp = SUBSTRING(@cpf_temp,1,3) + '.' + SUBSTRING(@cpf_temp,4,3) + '.' + SUBSTRING(@cpf_temp,7,3) + '-' + SUBSTRING(@cpf_temp,10,2) END --fim_se RETURN (@cpf_temp) END; GO -- chamada da function --SELECT dbo.usf_formata_cpf('481604472z',0); --SELECT dbo.usf_formata_cpf('0541371479',1); --SELECT dbo.usf_formata_cpf('05413714793',1); --SELECT dbo.usf_formata_cpf(18404,1); --SELECT dbo.usf_formata_cpf(18404,0);
Validar CPF em SQL
Como validar CPF em SQL Server, store function para validar CPF em T-SQL.
Esta store function, foi desenvolvida pensando no armazenamento do CPF em BIGINT, mas pode ser usada facilmente sem adaptações com CPF tipo VARCHAR(11) sem mascaras ou formatações.
Recomendável persistência do CPF em BIGINT por motivo principal de desempenho.
Segue function....
IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usf_valida_cpf]') AND type IN (N'FN') ) DROP FUNCTION dbo.usf_valida_cpf; GO CREATE FUNCTION dbo.usf_valida_cpf(@cpf varchar(max)) RETURNS bit AS -- Nome Artefato/Programa..: usf_valida_cpf.sql -- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) baseado em Script feito por: -- ........................: Cristiano Martins Alves em http://www.devmedia.com.br -- Data Inicio ............: 19/05/2011 -- Data Atualizacao........: 21/02/2012 -- Versao..................: 0.02 -- Compilador/Interpretador: T-SQL (Transact SQL) -- Sistemas Operacionais...: Windows -- SGBD....................: MS SQL Server 2005/2008 -- Kernel..................: Nao informado! -- Finalidade..............: Store Procedure (Function) para validar o numero do CPF -- OBS.....................: A entrada é um varchar e o retorno é um bit, 1 para válido, 0 para inválido e null para nulos -- ........................: -- BEGIN DECLARE @index int, @sumx int, @dig1 int, @dig2 int, @cpf_temp varchar(11), @cpfx varchar(11), @dig_equal bit, --0 para negativa e 1 para afirmativa @r bit -- Pre-validacao 1, se e nulo, entao retorna nulo IF @cpf IS NULL BEGIN SET @r = NULL RETURN (@r) END --fim_se -- Pre-validacao 2, se e maior que 11 digitos , entao retorna 0 IF LEN(@cpf) > 11 BEGIN SET @r = 0 RETURN (@r) END --fim_se -- Pre-validacao 3, se e tem alguma letra no cpf, entao retorna 0 IF (SELECT CASE WHEN patindex('%[^0-9]%', @cpf) > 0 THEN 1 ELSE 0 END) = 1 BEGIN SET @r = 0 RETURN (@r) END --fim_se -- Pre-validacao 4, se e menor que 11 digitos , pode ser oriundo de bigint, então fazer tratamento de zeros SET @cpfx = @cpf IF LEN(@cpf) < 11 BEGIN SET @cpfx = REPLICATE('0',11-LEN(@cpf))+@cpf END --fim se /* -- Pre-validcao 5, se a sequencia tem numeros iguais, entao retorna 0 Verificando se os digitos sao iguais. A Principio CPF com todos os numeros iguais sao invalidos, apesar de validar o calculo do digito verificador EX: O CPF 00000000000 é inválido, mas pelo calculo seria valido */ SET @cpf_temp = SUBSTRING(@cpfx,1,1) SET @index = 1 SET @dig_equal = 1 WHILE (@index <= 11) BEGIN IF SUBSTRING(@cpfx,@index,1) <> @cpf_temp BEGIN SET @dig_equal = 0 END SET @index = @index + 1 END; --Caso os digitos nao sejam todos iguais, comeca o calculo dos digitos IF @dig_equal = 0 BEGIN --Calculo do 1º digito SET @sumx = 0 SET @index = 1 WHILE (@index <= 9) BEGIN SET @sumx = @sumx + CONVERT(int,SUBSTRING(@cpfx,@index,1)) * (11 - @index) SET @index = @index + 1 END SET @dig1 = 11 - (@sumx % 11) IF @dig1 > 9 BEGIN SET @dig1 = 0 END -- Calculo do 2º digito SET @sumx = 0 SET @index = 1 WHILE (@index <= 10) BEGIN SET @sumx = @sumx + CONVERT(int,SUBSTRING(@cpfx,@index,1)) * (12 - @index) SET @index = @index + 1 END --fim_enquanto SET @dig2 = 11 - (@sumx % 11) IF @dig2 > 9 BEGIN SET @dig2 = 0 END -- Validando IF (@dig1 = SUBSTRING(@cpfx,LEN(@cpfx)-1,1)) AND (@dig2 = SUBSTRING(@cpfx,LEN(@cpfx),1)) BEGIN SET @r = 1 END ELSE BEGIN --senao SET @r = 0 END --fim_se END ELSE BEGIN --senao SET @r = 0 -- invalido END --fim_se RETURN (@r) END; GO -- chamada da function --SELECT dbo.usf_valida_cpf('80481604472'); -- retorna 1 -- ou --SELECT dbo.usf_valida_cpf(80481604472); -- retorna 1 -- ou --SELECT dbo.usf_valida_cpf('05413714793'); -- retorna 1 -- ou --SELECT dbo.usf_valida_cpf(05413714793); -- retorna 1
segunda-feira, 12 de março de 2012
Validando Cadastro Específico do INSS - CEI em SQL Server
Validando Cadastro Específico do INSS - CEI em SQL Server, como validar CEI em SQL Server usando functions do TRANSACT SQL
-- Nome Artefato/Programa..: usf_valida_cei.sql -- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) -- Data Inicio ............: 11/03/2012 -- Data Atualizacao........: 11/03/2012 -- Versao..................: 0.01 -- Compilador/Interpretador: T-SQL (Transact SQL) -- Sistemas Operacionais...: Windows -- SGBD....................: MS SQL Server 2005/2008 -- Kernel..................: Nao informado! -- Finalidade..............: Store Procedure (Function) para validar numero de CEI (Cadastro Especifico de INSS) -- OBS.....................: A entrada é um varchar e o retorno é um bit, 1 para válido, 0 para inválido e null para nulos -- ........................: -- IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usf_valida_cei]') AND type IN (N'FN') ) DROP FUNCTION usf_valida_cei; GO CREATE FUNCTION usf_valida_cei(@number varchar(max)) RETURNS bit AS /* CEI - Cadastro Específico do INSS O algorítmo abaixo demonstra como obter o dígito verificador da matricula CEI. Formato : EE.NNN.NNNNN/AD Onde: EE - Número NNNNNNNN - Número A - Atividade D - Dígito Verificador a) Multiplicar os últimos 11 algarismos pelos seus respectivos pesos, conforme abaixo: Pesos: 7,4,1,8,5,2,1,6,3,7,4 Algarismos: EENNNNNNNNA Cálculo 7 * E = X1 4 * E = X2 1 * N = X3 8 * N = X4 5 * N = X5 2 * N = X6 1 * N = X7 6 * N = X8 3 * N = X9 7 * N = X10 4 * A = X11 D (posição do dígito) b) Somar todos os produtos obtidos no item "a" Soma = X1+2+X3+X4+X5+X6+X7+X8+X9+X10+X11 c) Com o total obtido no item "b", somar o algarismo da unidade com o algarismo da dezena. Total = Dezena de soma + Unidade de soma d) Subtrair de 10 o algarismo da unidade do obtido no item "c". Resultado = 10 - Unidade de Total O algarismo da unidade do resultado da subtração será o dígito verificador. Digito verificador = Unidade de Resultado */ BEGIN DECLARE @weight varchar (11) DECLARE @total int DECLARE @c int DECLARE @result int DECLARE @r bit DECLARE @number_dig int DECLARE @number_dig_c int DECLARE @number_dig_x varchar(2) SET @weight = '74185216374' -- peso estabelecido para gerar CEI SET @total = 0 SET @c = 1 SET @number_dig = substring(@number,len(@number),1) -- ultimo digito extraido do parametro informado --fazendo um uma pre-validação do CEI, validação 1 se é nulo IF @number IS NULL BEGIN SET @r = NULL RETURN (@r) END --fim_se --fazendo um uma pre-validação do CEI, validação 2 se o tamanho é diferente de 12 digitos IF LEN(@number) > 12 BEGIN SET @r = 0 RETURN (@r) END --fim_se --fazendo um uma pre-validação do CEI, validação 3 se não for nulo e for mair que 12, -- ainda pode ser uma string IF (SELECT CASE WHEN patindex('%[^0-9]%', @number) > 0 THEN 1 ELSE 0 END) = 1 BEGIN SET @r = 0 RETURN (@r) END --fim_se --fazendo um uma pre-validação do CEI, validação 4 se está vindo c/ 12 dig zerados IF CAST(@number AS bigint)=0 BEGIN SET @r = 0 RETURN (@r) END --fim_se WHILE (@c<=11) BEGIN SET @result = cast (substring(@number, @c, 1) AS int) * cast(substring(@weight,@c, 1) AS int) SET @total = @total + @result SET @c = @c + 1 END -- fim_enquanto, neste caso, while simulando for SET @number_dig_x = right(left(cast (@total as varchar(max)), 12), 2) SET @number_dig_c = (cast(left(@number_dig_x, 1) as int)) + (cast(right(@number_dig_x, 1) as int)) SET @number_dig_c = 10 - (cast (Right(Left(@number_dig_c, 12), 1) as int)) IF (@number_dig_c > 9) BEGIN SET @number_dig_c = 0 END ELSE BEGIN SET @number_dig_c = 10 - @number_dig_c END IF (@number_dig <> @number_dig_c) BEGIN SET @r = 0 END ELSE BEGIN SET @r = 1 END RETURN (@r) END; GO -- Exemplo de chamada da function -- SELECT dbo.usf_valida_cei('741852163748');
Assinar:
Postagens (Atom)