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
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:
Comentários (Atom)