Formatar CNPJ em SQL

Store function para formatar CNPJ em SQL Server

IF EXISTS (
            SELECT * 
              FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[usf_formata_cnpj]') 
               AND type IN (N'FN')
           )
 DROP FUNCTION dbo.usf_formata_cnpj;
GO
 
CREATE FUNCTION dbo.usf_formata_cnpj(@cnpj varchar(max), @mascara bit) RETURNS varchar(max) 
AS
-- Nome Artefato/Programa..: usf_formata_cnpj.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) 
-- Data Inicio ............: 19/09/2012
-- Data Atualizacao........: 19/09/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 CNPJ
-- OBS.....................: A entrada é um varchar e o retorno é um varchar formatado do CNPJ, parametro mascara setado em 0 apenas string com zeros, 1 formata o CNPJ de fato 
-- ........................: 
--  
BEGIN
 
  DECLARE @cnpj_temp varchar(max) 
   
  -- Pre-validacao 1, se e nulo, entao retorna nulo
  IF @cnpj IS NULL BEGIN
     RETURN (@cnpj)  
  END --fim_se      
 
  -- Pre-validacao 2, se e maior que 14 digitos , entao retorna 0 
  IF LEN(@cnpj) > 14 BEGIN
     RETURN (@cnpj)
  END --fim_se
   
  -- Pre-validacao 3, se e tem alguma letra no CNPJ, entao retorna 0 
  IF (SELECT CASE WHEN patindex('%[^0-9]%', @cnpj) > 0 THEN 1 ELSE 0 END) = 1 BEGIN
     RETURN (@cnpj)
  END --fim_se  
 
  -- Pre-validacao 4, se e menor que 14 dig, pode ser oriundo de um bigint, entao colocar zeros a frente
  SET @cnpj_temp = @cnpj
  IF LEN(@cnpj) < 14 BEGIN
     SET @cnpj_temp = REPLICATE('0',14-LEN(@cnpj))+@cnpj
  END --fim_se 
   
  -- Se e para formatar mesmo 
  IF @mascara = 1 BEGIN
     SET @cnpj_temp = SUBSTRING(@cnpj_temp,1,2) + '.' + SUBSTRING(@cnpj_temp,3,3) + '.' + SUBSTRING(@cnpj_temp,6,3) + '/' + SUBSTRING(@cnpj_temp,9,4) + '-' + SUBSTRING(@cnpj_temp,13,2) 
  END --fim_se 
   
  RETURN (@cnpj_temp) 
   
END;
GO
 
-- chamada da function 

-- test 1
SELECT dbo.usf_formata_cnpj('00000000000191',1); -- Banco do Brasil Sede - 00.000.000/0001-91
SELECT dbo.usf_formata_cnpj('00000000000191',0); -- Banco do Brasil Sede - 00000000000191
-- test 2 
SELECT dbo.usf_formata_cnpj('191',1); -- Banco do Brasil Sede - 00.000.000/0001-91
SELECT dbo.usf_formata_cnpj('191',0); -- Banco do Brasil Sede - 00000000000191

Validar CNPJ em SQL

Como validar CNPJ em SQL Server, store function para validar CNPJ em T-SQL (Transact-SQL)

Esta store function, foi desenvolvida pensando no armazenamento do CNPJ em BIGINT, mas pode ser usada facilmente sem adaptações com CNPJ tipo VARCHAR(14) sem mascaras ou formatações.

Recomendável persistência do CNPJ em BIGINT por motivo principal de desempenho.

Segue function....

IF EXISTS (
            SELECT * 
              FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[usf_valida_cnpj]') 
               AND type IN (N'FN')
           )
 DROP FUNCTION dbo.usf_valida_cnpj;
GO
 
CREATE FUNCTION dbo.usf_valida_cnpj(@cnpjx varchar(14)) RETURNS bit
AS
-- Nome Artefato/Programa..: usf_valida_cnpj.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) baseado em Script feito por:
-- ........................: Baseado em código de Fernando Jacinto da Silva em http://www.devmedia.com.br
-- Data Inicio ............: 18/09/2011
-- Data Atualizacao........: 19/09/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 CNPJ
-- OBS.....................: A entrada é um varchar e o retorno é um bit, 1 para válido, 0 para inválido e null para nulos 
-- ........................: 
--  
BEGIN

DECLARE
        @cnpj   varchar(14) 
      , @indice int
      , @soma   int
      , @dig1   int
      , @dig2   int
      , @var1   int
      , @var2   int
      , @resultado bit

    SET @cnpj = @cnpjx 
    SET @soma = 0
    SET @indice = 1
    SET @resultado = 0

    -- pre-validacao 1, se e nulo, entao retorna nulo
    IF @cnpj IS NULL BEGIN
        SET @resultado = NULL
        RETURN (@resultado)  
    END --fim_se      
 
    -- pre-validacao 2, se e maior que 11 digitos , entao retorna 0 
    IF LEN(@cnpj) > 14 BEGIN
        SET @resultado = 0 
        RETURN (@resultado)
    END --fim_se
   
    -- pre-validacao 3, se e tem alguma letra no cpf, entao retorna 0 
    IF (SELECT CASE WHEN patindex('%[^0-9]%', @cnpj) > 0 THEN 1 ELSE 0 END) = 1 BEGIN
       SET @resultado = 0
       RETURN (@resultado)
    END --fim_se   
 
    -- pre-validacao 4, se e menor que 11 digitos , pode ser oriundo de bigint, então fazer tratamento de zeros 
    IF LEN(@cnpj) < 14 BEGIN
        SET @cnpj = REPLICATE('0',14-LEN(@cnpj))+@cnpj
    END --fim se 
 
    /* algorítimo para o primeiro dígito 543298765432 */
    /* cálculo do 1º dígito */
    /* cálculo da 1ª parte do algorítiom 5432 */

    SET @var1 = 5 /* 1a parte do algorítimo começando de "5" */

    WHILE (@indice <= 4) BEGIN

        SET @soma = @soma + CONVERT(int,SUBSTRING(@cnpj,@indice,1)) * @var1

        SET @indice = @indice + 1 /* navegando um-a-um até < = 4, as quatro primeira posições */

        SET @var1 = @var1 - 1  /* subtraindo o algorítimo de 5 até 2 */

    END

    /* cálculo da 2ª parte do algorítiom 98765432 */

    SET @var2 = 9

    WHILE (@indice <= 12) BEGIN

        SET @soma = @soma + CONVERT(int,SUBSTRING(@cnpj,@indice,1)) * @var2

        SET @indice = @indice + 1

        SET @var2 = @var2 - 1            
    END

    SET @dig1 = (@soma % 11)

    /* se o resto da divisão for < 2, o digito = 0 */

    IF @dig1 < 2 BEGIN

        SET @dig1 = 0

    END ELSE BEGIN /* se o resto da divisão não for < 2*/

        SET @dig1 = 11 - (@soma % 11)
    END

    /* cálculo do 2º dígito */
    /* zerando o indice e a soma para começar a calcular o 2º dígito*/   

    SET @indice = 1
    SET @soma = 0

    /* cálculo da 1ª parte do algorítiom 65432 */

    SET @var1 = 6 /* 2a parte do algorítimo começando de "6" */

    SET @resultado = 0  

    WHILE (@indice <= 5) BEGIN
  
        SET @soma = @soma + CONVERT(int,SUBSTRING(@cnpj,@indice,1)) * @var1
        SET @indice = @indice + 1 /* navegando um-a-um até < = 5, as quatro primeira posições */
        SET @var1 = @var1 - 1       /* subtraindo o algorítimo de 6 até 2 */

    END

    /* cálculo da 2ª parte do algorítiom 98765432 */
    SET @var2 = 9

    WHILE (@indice <= 13) BEGIN

        SET @soma = @soma + CONVERT(int,SUBSTRING(@cnpj,@indice,1)) * @var2
        SET @indice = @indice + 1
        SET @var2 = @var2 - 1            

    END
 
    SET @dig2 = (@soma % 11) 

    /* se o resto da divisão for < 2, o digito = 0 */

    IF @dig2 < 2 BEGIN

        SET @dig2 = 0

    END ELSE BEGIN /* se o resto da divisão não for < 2*/

        SET @dig2 = 11 - (@soma % 11)
    END
 
    -- validando

    IF (@dig1 = SUBSTRING(@cnpj,LEN(@cnpj)-1,1)) AND (@dig2 = SUBSTRING(@cnpj,LEN(@cnpj),1)) BEGIN

        SET @resultado = 1

    END ELSE BEGIN

        SET @resultado = 0 
  
    END 

RETURN (@resultado)

END;
GO

-- chamada da function 

-- test 1
SELECT dbo.usf_valida_cnpj('00000000000191'); -- Banco do Brasil Sede - 1 (válido)
-- test 2 
SELECT dbo.usf_valida_cnpj('191'); -- Banco do Brasil Sede - 1 (válido)
-- test 3
SELECT dbo.usf_valida_cnpj('00000000000192'); -- Banco do Brasil Sede - 0 (inválido) 

Cache de memória do SQL Server

O cache de memória, é uma área de memória reservada pelo SQL Server, com o objetivo de se evitar ao máximo acesso ao disco magnético, pois o barramento deste periférico é bem inferior ao da memória e do processador, analogamente seria como sistema de encanamento com canos pequenos e grandes, mas para manter a vazão cria-se um caixa de água na mudança de diâmetro desses canos, o cache de memória é a caixa de água, sendo assim a entrada e a saída no meio magnético (Imput-Output, I/O) será reduzida, acelerando a execução de procedures, transações, querys que são processadas com maior maior frequência.

Quando o SQL Server começa apresentar lentidão e as operações começam a mostrar erros devido à falta de memória, ou muitas operações de I/O, neste caso recomenda-se, verificar a necessidade de redimensionar a memória do servidor SQL Server.

No momento que o servidor SQL Server é reinicializado toda a memória cache é automaticamente zerada, isto é limpa, porém em servidor de produção, esse procedimento torna-se inviável, neste cenário o ideal seria redimensionar o servidor, porém como forma paliativa, são sugeridos três comandos que podem ser úteis, são eles:

-- limpa as entradas do cache do sistema
DBCC FREESYSTEMCACHE; 
-- limpa as entradas do cache da sessao 
DBCC FREESESSIONCACHE;
-- elimina todas as entradas do cache de procedures
DBCC FREEPROCCACHE;

Quando executado os três comandos acima no servidor, será limpado a memória cache e por conseguinte liberado memória de forma imediata para o SQL Server.

Outra forma de liberar memória cache é na otimização de consultas no SQL Server, bem comum compararmos diferentes formas de construção de uma consulta (query), normalmente dentro de uma mesma sessão no SQL Server, por examplo usando MS SQL Server Management Studio.

O grande moído é que a primeira consulta (query) a ser executada, sempre é mais rápido do que a primeira, pois na primeira vez em que é executada ela é armazenada na memória cache e já segunda em diante a execução se baseia na memória cache.

Analisando com critério a query executada, faz-se necessário sempre evitar o uso do cache, ao menos no momento de otimização de consultas.

Três comandos simples a ser executado na sessão Management Studio para solucionar, eliminar, desprezar o cache de memória, são eles:

-- elimina paginas de buffer limpas
DBCC DROPCLEANBUFFERS;
-- elimina todas as entradas do cache de procedures
DBCC FREEPROCCACHE;
-- limpa as entradas de cache nao utilizadas 
DBCC FREESYSTEMCACHE ( 'ALL' );

A dica, é executar esses três comandos supracitados, no momento de otimizar querys.

Fique na Paz do Senhor Jesus!

Consulta SQL de Plano de Contas - Query Contabil - Query para Centro de Custo

A idéia primordial desse artigo é demonstrar como desenvolver uma query sql usando um plano de contas ou centro de custo, o principio é o mesmo, subtotalizando de forma invertida, considerando uma estrutura de balancete ou centro de custo, mas sempre usando um plano de contas com contas analiticas e sintéticas e vários niveis de sub-contas.

O SGBDR testados foram:
 Firebird 2.5.1
 Postgres 9.1
 SQL Server 2012
 Oracle 11g R2

Expondo o problema, exemplo:

Montar uma query relatório que mostre o resultado totalizado por níveis de contas lançadas.
O centro de custo irá ser lançado sempre no ultimo nivel e/ou conta analitica, ou seja 1.1.1 ou 1.2.1, etc. 

Conforme figura Figura 01, abaixo:

Figura 01 - Relatório Sumarizado por Plano de Contas

Temos uma estrutura de centro de custo modelada da seguinte maneira: 

Script para Firebird 2.5.1, Postgres 9.1, SQL Server 2012, Oracle 11g R2

-- USE tempdb; -- Descomentar caso use SQL Server 
--DROP TABLE centro_custo;
CREATE TABLE centro_custo
( 
   id_centro_custo  varchar(12) PRIMARY KEY   -- dados da conta ex. 1.02.01 
 , descricao        varchar(50)               -- descricao da conta cadastrada ex. Vendas Externas
 , tipo_conta       varchar(1)                -- tipo de conta do cc Analitica ou Sintética, dominio discreto: A ou S, em situação de produção merece uma constraint check
); 

--DROP TABLE movimento;
CREATE TABLE movimento 
(
   id_movimento   integer   PRIMARY KEY  -- id do movimento, recomenda-se auto incremento, mas para simplifcar fica sem auto incremento
 , numero_doc   varchar(40)              -- numero do documento a ser informado  
 , id_centro_custo   varchar(12)         -- chave estrangeira para a tabela centro de custo, mas para simplificar apenas iremos convencionar, não será habilitado a FK, recomendo colocar not null
 , valor_movimento numeric(15,2)         -- valor informado 
);

Agora iremos povoar a tabela de centro de custo

-- Receitas 

INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1','Receita','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.1','Vendas Internas','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.1.1','Escola','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.1.2','Escritório','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.2','Vendas Externas','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.2.1','Livro','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.2.2','Brinquedos','A');

-- Despesas

INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2','Despesas','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.1','Fornecedores','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.1.1','Nacional','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.1.2','Importado','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.2','Escritório','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.2.1','Materiais de limpeza','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.2.2','Materiais de Escritório','A');

-- Vamos povoar a tabela movimento: 

INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (1,'0000021','1.1.2',50.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (2,'0000042','1.2.2',100.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (3,'0000084','1.2.2',160.00);

INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (4,'0000142','2.2.1',40.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (5,'0000210','2.2.2',80.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (6,'0000242','2.2.2',20.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (7,'0000284','2.2.1',15.00);

Listando os centros de custos cadastrados ...

-- Listando conteudo de centro_custo 
SELECT * FROM centro_custo ORDER BY 1;

Figura 02 - Lista de Centro Custo

Listando os movimentos cadastrados, referenciando os centros de custos ...

-- Listando conteudo de movimento 
SELECT * FROM movimento;

Figura 03 - Lista de Movimento Script apenas para SQL Server 2012

-- Query SQL Centro de Custo, para SQL Server 2012, exemplo: 
  SELECT cc.id_centro_custo
       , cc.descricao 
       , sum(m.valor_movimento) AS total_conta 
    FROM centro_custo cc 
    JOIN movimento m
      ON m.id_centro_custo LIKE cc.id_centro_custo + '%'  -- O segredo está aqui, o campo id_centro_custo vem depois do LIKE
GROUP BY cc.id_centro_custo 
       , cc.descricao 
ORDER BY cc.id_centro_custo ASC
       ; 

Figura 04 - Query Centro Custo com Resultado em SQL Server 2012

Script apenas para Firebird 2.5.1, Postgres 9.1, Oracle 11g R2

  
-- Query SQL Centro de Custo, para Firebird 2.5.1, Postgres 9.1, Oracle 11g R2, exemplo: 
  SELECT cc.id_centro_custo
       , cc.descricao 
       , sum(m.valor_movimento) AS total_conta 
    FROM centro_custo cc 
    JOIN movimento m
      ON m.id_centro_custo LIKE cc.id_centro_custo || '%'  -- O segredo está aqui, o campo id_centro_custo vem depois do LIKE
GROUP BY cc.id_centro_custo 
       , cc.descricao 
ORDER BY cc.id_centro_custo ASC
       ; 

Figura 05 - Query Centro Custo com Resultado em Oracle 11g R2

A grande dica está na junção dos campos de conta usando like concatenado com '%' no final

Como no exemplo com Oracle 11g R2, abaixo descrito:

ON m.id_centro_custo LIKE cc.id_centro_custo || '%'

Existem outras formas de se chegar ao mesmo resultado, uma delas seria implementar union em contas de grupo e depois criar uma view, mas, fica complicado e não elegante, já que, será necessário alterar a query toda vez que o plano de contas mudar a estrutura.

Outra forma seria usar querys recursivas, que seria bem mais elegante que a opção anterior, entretanto, mais complexa, mas quanto ao uso de recursividade em querys, nem todos os SGBDRs atualmente dão suporte a essa técnica, outro problema reside no fato do desempenho e consumo de recursos, pois quanto mais níveis tiver o plano de contas maior será a pilha, em outras palavras, consumo de memória e processamento no servidor do banco de dados alto.

Eu particularmente já tive a oportunidade fazer parte de uma equipe de desenvolvimento, em que o meu chefe e a própria equipe insistiram em traçar uma modelagem que usava querys recursivas, apesar de não concordar, como era novato na equipe, não me deram crédito, então me reservei a sabedoria do silêncio e mesmo não concordando aproveitei a oportunidade para fazer acontecer e vê como ficaria um sistema de custos com uso maciço de querys recursivas, foi divertido, mas ainda não recomendo, pois a complexidade é alta das querys recursivas e da codificação da aplicação também, tornando o compartilhamento do conhecimento difícil, como também a manutenção da aplicação, considerando ainda o consumo extremo de memória e processamento a nível de infra-estrutura.

Mais uma vez espero ter ajudado.


Fique na Paz do Senhor Jesus Cristo !!!

Storing and Retrieving Images from SQL Server using Store Procedures in Transact-SQL - Armazenando e Recuperando Imagens do SQL Server usando Store Procedures em Transact-SQL

In SQL Server does not have the capability to export a varbinary (BLOB) images directly via SQL, for the Operating System (Windows), just as import, export is done via SSRS or using any programming language, but it was developed a stored procedure called usp_lo_export, below, follows the solution to the problem in question.

No SQL Server não tem o recurso para exportar um varbinary (blob), imagens, diretamente via SQL, para o Sistema Operacional (Windows), apenas como importar, a exportação é feita via SSRS ou usando alguma linguagem de programação, mas para isso foi desenvolvido uma store procedure chamada usp_lo_export, abaixo, segue a solução para o problema em questão.

Configuration for SQL Server 2008/2012

-- 
-- Functionality over safety
-- Funcionalidade em detrimento da segurança

-- Routine Tested in SQL SERVER 2008 Enterprise and SQL SERVER 2012 Enterprise
-- Procedimento Testado no SQL SERVER 2008 Enterprise e SQL SERVER 2012 Enterprise

-- Configuration for SQL Server procedure call xp_cmdshell
-- Configuracao do SQL Server para chamada de procedure xp_cmdshell
USE master; 
GO

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

-- make dir c:\images
EXEC xp_cmdshell 'mkdir c:\images';

-- list dir c:\images 
EXEC xp_cmdshell 'dir c:\images\*.*';

Store Procedure usp_lo_export

USE master; -- change to your database - mudar para o seu bd
GO
-- Creating Stored Procedure
-- start procedure 
IF EXISTS ( SELECT name FROM sys.objects  WHERE object_id = OBJECT_ID(N'[dbo].[usp_lo_export]') AND type IN (N'P') )
 DROP PROCEDURE usp_lo_export;
GO
CREATE PROCEDURE usp_lo_export (@bdx varchar(65), @schemax varchar(65), @tablex varchar(65), @col_bin_imgx varchar(65), @pk_tablex varchar(65), @idx bigint, @file_namex varchar(255))
AS
-- Artifact Name/Program...: usp_lo_exportx.sql
-- Author..................: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com)
-- Date Start..............: 28/08/2012
-- Date Update.............: 08/12/2014
-- Version.................: 0.03
-- Compiler/Interpreter....: T-SQL (Transact SQL) 
-- OS......................: Windows
-- RDBMS...................: MS SQL Server 2008/2012
-- Kernel..................: Not informed! 
-- Porpuse - Finalidade....: Stored Procedure to export images (varbinary) to binary file database for SO - SP para exportar imagens (varbinary) para arquivo binário do banco de dados para o SO 
-- Notice .................: The parameters are: db, schema, table name, column type binary, primary key, primary key id and name of the binary file -
-- ........................: Os parametros são: bd, esquema, nome da tabela, coluna do tipo binaria, chave primária, id da chave primaria e nome do arquivo binario
--  
BEGIN
 SET NOCOUNT ON
 BEGIN TRY
   
  DECLARE @sql_stmt varchar(4000)
        , @sql_query  varchar(4000) 
        , @v_sql      varchar(4)
        , @echo_v_sql varchar(255)
        , @dir_bcp varchar(255) 
 
  -- example of using bcp into SQL Server 2008 to form Bcp.fmt file (set in Y last option)
  -- exemplo de uso de bcp em SQL Server 2008, para formar archive bcp.fmt (setar Y na ultima opcao) 
  -- bcp testdb.dbo.uvw_img out c:\images\test1.png -T 
  -- bcp.fmt created - ja criado 
  -- bcp testdb.dbo.uvw_img out c:\images\test1.png -T -f c:\images\bcp.fmt     
 
  -- model fmt file bcp (bcp.fmt) to import binary, SQL SERVER 2008 
  -- modelo de arquivo fmt do bcp (bcp.fmt) para importação binaria, SQL SERVER 2008 
  --10.0
  --1
  --1       SQLBINARY           0       0       ""   1     archive            ""
     
 
  -- generating configuration file
  -- gerando arquivo de configuracao 
  SET @v_sql      = SUBSTRING(CAST (SERVERPROPERTY('productversion') AS VARCHAR(max)),1,4) -- version sql server 
  SET @echo_v_sql = 'ECHO ' + @v_sql + ' > c:\images\conf_blob.fmt'
    
  EXEC xp_cmdshell @echo_v_sql, no_output
  EXEC xp_cmdshell 'ECHO 1 >> c:\images\conf_blob.fmt', no_output
  EXEC xp_cmdshell 'ECHO 1       SQLBINARY           0       0       ""   1     archive            "" >> c:\images\conf_blob.fmt', no_output
 
  IF object_id('tempdb.dbo.##blobx') IS NOT NULL BEGIN
       DROP TABLE ##blobx
  END
  CREATE TABLE ##blobx
  (
       file_binx varbinary(max)
  )
   
  -- populating global temporary table with the table data, where the blob
  -- povoando tabela temporária global com os dados da tabela, onde se encontra o blob
  SET @sql_query = 'INSERT INTO tempdb.dbo.##blobx
                    SELECT ' + @col_bin_imgx  + '
                      FROM ' + @bdx + '.' + @schemax + '.' + @tablex + '
                     WHERE ' + @pk_tablex + ' = ' + ltrim(str(@idx)) 
  PRINT @sql_query
  EXEC (@sql_query)

            
  -- for use with dynamic sql xp_cmdshell SP - sql dinamico para uso com SP xp_cmdshell
  -- bcp tempdb.dbo.##blobx out c:\images\test1.png -T -f c:\images\bcp.fmt
  IF @v_sql = '10.0' BEGIN
       SET @dir_bcp = '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp"'
  END IF @v_sql = '11.0' BEGIN
       SET @dir_bcp = '"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp"'
  END ELSE BEGIN
       SET @dir_bcp = 'bcp'
  END
   
  SET @sql_stmt = @dir_bcp + ' tempdb.dbo.##blobx out c:\images\' + ltrim(str(@idx)) + '_' + @file_namex + ' -T -f c:\images\conf_blob.fmt'
 
  PRINT 'Exporting binary ...'
  EXEC xp_cmdshell @sql_stmt, no_output
       
 END TRY
 
 BEGIN CATCH
  SELECT ERROR_NUMBER()  AS error_number
       , ERROR_SEVERITY()  AS error_severity
       , ERROR_STATE()  AS error_state
       , ERROR_PROCEDURE() AS error_procedure
       , ERROR_LINE()   AS error_line
       , ERROR_MESSAGE()  AS error_message;    
 END CATCH;
  
 -- cleaning dirt, eliminating temporary table - limpando sujeira, eliminando tabela temporaria
 IF object_id('tempdb.dbo.##blobx') IS NOT NULL BEGIN
      DROP TABLE ##blobx
 END 
 -- cleaning, eliminating the bcp format file - limpando sugeira, eliminando arquivo de formatacao do bcp 
 EXEC xp_cmdshell 'DEL c:\images\conf_blob.fmt', no_output
 
 SET NOCOUNT ON
END; 
GO 
-- end procedure 

Working with stored procedure usp_lo_export

USE testdb; -- change to your database - mudar para o seu bd
GO

-- Creating Table Test
IF EXISTS ( SELECT name FROM sys.objects  WHERE object_id = OBJECT_ID(N'[dbo].[image]') AND type IN (N'U') )
 DROP TABLE image;
GO
CREATE TABLE image
(
   id_image int identity(1,1) NOT NULL  PRIMARY KEY
 , file_name nvarchar(1000) NOT NULL
 , archive varbinary(max) -- binary type, implemented in sql2005 sp2
);
GO

-- Only test - Apenas para teste
IF EXISTS ( SELECT name FROM sys.objects  WHERE object_id = OBJECT_ID(N'[dbo].[uvw_img]') AND type IN (N'V') )
 DROP VIEW uvw_img;
GO
CREATE VIEW uvw_img AS
SELECT TOP 1 archive FROM image
GO

-- If you need to test again - Caso precise fazer teste novamente
--TRUNCATE TABLE image;


-- Populating table with images - Povoando tabela com imagens
INSERT INTO image (file_name, archive) 
SELECT 'koala.jpg', BULKColumn FROM OPENROWSET(BULK N'C:\Users\Public\Pictures\Sample Pictures\koala.jpg', SINGLE_BLOB) AS image;

INSERT INTO image (file_name, archive) 
SELECT 'desert.jpg', BULKColumn FROM OPENROWSET(BULK N'C:\Users\Public\Pictures\Sample Pictures\desert.jpg', SINGLE_BLOB) AS image;

INSERT INTO image (file_name, archive) 
SELECT 'penguins.jpg', BULKColumn FROM OPENROWSET(BULK N'C:\Users\Public\Pictures\Sample Pictures\penguins.jpg', SINGLE_BLOB) AS image;

INSERT INTO image (file_name, archive) 
SELECT 'plan_test.xlsx', BULKColumn FROM OPENROWSET(BULK N'C:\Users\Public\Documents\plan_test.xlsx', SINGLE_BLOB) AS Image;
GO

-- Listing images
SELECT * FROM image;


-- Call the Store Procedure - Examples
-- Ex 01 - correct data reported
EXEC master.dbo.usp_lo_export 'testdb','dbo','image','archive','id_image',1,'koala.jpg';
EXEC master.dbo.usp_lo_export 'testdb','dbo','image','archive','id_image',2,'desert.jpg';
EXEC master.dbo.usp_lo_export 'testdb','dbo','image','archive','id_image',3,'penguins.jpg';

SELECT * FROM image;

-- Ex 02 - Table wrongly informed - Tabela informada errada 
EXEC master.dbo.usp_lo_export 'testdb','dbo', 'imagex','archive','id_image',3,'penguins.jpg';

-- Ex 03 - Correct data reported, electronically excel spreadsheet - Dados informados corretos, planilha eletronica do excel 
EXEC master.dbo.usp_lo_export 'testdb','dbo','image','archive','id_image',4,'plan_excel.xlsx';

-- Ex 04 - Use with anonymous block - Uso com bloco anônimo 
DECLARE @file_namex varchar(255)
      , @idx bigint 
SET @idx = 1
SELECT @file_namex = file_name FROM image WHERE id_image = @idx
EXEC master.dbo.usp_lo_export 'testdb','dbo','image','archive','id_image', @idx, @file_namex; 

The peace of the Lord Jesus

Algoritmos, Caixa Eletrônico em SQL SERVER

Anteriormente em setembro/2010, havia escrito um script para caixa eletrônico em linguagem PL/PgSQL do Postgres, agora foi desenvolvido o mesmo algoritmo de caixa eletrônico para a linguagem Transact do SQL SERVER.

O parâmetro é o valor em inteiro no qual retorna as cédulas das notas em Real.

Com poucas adaptações, pode se remover a nota de 1 Real, para ficar com o novo padrão de cédulas da moeda Real do Brasil.

Mais uma vez, espero ter ajudado.

--Retornando notas do caixa eletrônico
--Notas de 1, 2, 5, 10, 20, 50 e 100

IF EXISTS (
            SELECT * 
              FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[usf_caixa_eletro]') 
               AND type IN (N'FN')
           )
 DROP FUNCTION usf_caixa_eletro;

CREATE FUNCTION usf_caixa_eletro (@pvalor int) RETURNS varchar(max) AS 
--
-- Nome Artefato/Programa..: usf_caixa_eletro_sql_server.sql
-- Autor(es)...............: O Peregrino (emersonhermann at gmail.com) 
-- Data Inicio ............: 02/08/2012
-- Data Atual..............: 03/08/2012
-- Versao..................: 0.01
-- Linguagem...............: TRANSACT 
-- Compilador/Interpretador: T-SQL 
-- Sistemas Operacionais...: Windows
-- SGBD....................: SQL SERVER 2005/2008/2012
-- Kernel..................: Nao informado!
-- Finalidade..............: Caixa Eletronico
-- OBS1....................: Caixa Eletronico
 
--
/*
Algoritmo Caixa Eletronico
notasSaída = []                         #guardar as notas que saírão do caixa eletrônico
notas = [100, 50, 20, 10, 5, 1]         #notas que podem ser sacadas
valor = 375                             #valor a ser sacado
 
restante = valor                        #faz uma cópia do valor em "restante"
inota = 0                               #índice da nota: 0 é 100, 1 é 50, 2 é 20, 3 é 10, ...
enquanto restante>0:                    #enquanto restante for maior que 0
  resultado = restante-notas[inota]     #calcula o resultado da subtração entre o valor e a nota
  se resultado<0:                       #se for negativo:
    inota++                             #incrementa o índice para a próxima nota
  senão:                                #se for positivo ou zero:
    restante = resultado                #deixa restante com o novo resultado
    notasSaída.adicionar(notas[inota])  #adiciona a nota utilizada nas que devem sair do caixa
 
para nota em notasSaída:                # escreve as notas que devem sair
  escreva nota
 
*/
BEGIN
 DECLARE
     @sretorno   varchar(max)
    ,@qnota1     integer
    ,@qnota2     integer
    ,@qnota5     integer
    ,@qnota10    integer
    ,@qnota20    integer
    ,@qnota50    integer
    ,@qnota100   integer
    ,@pvalorx    integer
    ,@residual   integer
    ,@restante   integer
    ,@vet_notas1 integer
    ,@vet_notas2 integer
    ,@vet_notas3 integer
    ,@vet_notas4 integer
    ,@vet_notas5 integer
    ,@vet_notas6 integer
    ,@vet_notas7 integer
    ,@i          integer
    ,@resultado  integer

    SET @vet_notas1=100
    SET @vet_notas2=50
    SET @vet_notas3=20
    SET @vet_notas4=10
    SET @vet_notas5=5
    SET @vet_notas6=2
    SET @vet_notas7=1
     
    SET @i         = 1
    SET @qnota1    = 0
    SET @qnota2    = 0
    SET @qnota5    = 0
    SET @qnota10   = 0
    SET @qnota20   = 0
    SET @qnota50   = 0
    SET @qnota100  = 0
    SET @pvalorx   = 0
    SET @resultado = 0
    SET @sretorno  = ''
    SET @restante  = @pvalor
 
    WHILE (@i <= 7) BEGIN
     
    
        IF @i = 1 BEGIN 
            SET @resultado = @restante - @vet_notas1
        END ELSE IF @i = 2 BEGIN 
            SET @resultado = @restante - @vet_notas2 
        END ELSE IF @i = 3 BEGIN 
            SET @resultado = @restante - @vet_notas3
        END ELSE IF @i = 4 BEGIN 
            SET @resultado = @restante - @vet_notas4
        END ELSE IF @i = 5 BEGIN 
            SET @resultado = @restante - @vet_notas5
        END ELSE IF @i = 6 BEGIN 
            SET @resultado = @restante - @vet_notas6
        END ELSE IF @i = 7 BEGIN 
            SET @resultado = @restante - @vet_notas7
        END 

     
        IF (@resultado < 0) BEGIN
          
           SET @i = @i + 1
               
        END ELSE BEGIN -- senao
 
           SET @restante = @resultado                             

           IF @i = 1 BEGIN
               SET @qnota100 = @qnota100 + 1
           END ELSE IF @i = 2 BEGIN
               SET @qnota50 = @qnota50 + 1
           END ELSE IF @i = 3 BEGIN
               SET @qnota20 = @qnota20 + 1
           END ELSE IF @i = 4 BEGIN
               SET @qnota10 = @qnota10 + 1
           END ELSE IF @i = 5 BEGIN
               SET @qnota5  = @qnota5 + 1
           END ELSE IF @i = 6 BEGIN
               SET @qnota2  = @qnota2 + 1
           END ELSE IF @i = 7 BEGIN
               SET @qnota1  = @qnota1 + 1
           END --fim_se 
              
        END -- fim_se 
  
    END -- fim_enquanto
     
     
    SET @sretorno = 'Total: '
                 + cast (@pvalor as varchar(max))
                 + ' ' --chr(10)
                 + 'Notas de 100:'
                 + cast (@qnota100 as varchar(max))
                 + ' ' --chr(10)
                 + 'Notas de 50:'
                 + cast (@qnota50 as varchar(max))
                 + ' ' --chr(10)
                 + 'Notas de 20:'
                 + cast (@qnota20 as varchar(max))
                 + ' ' --chr(10)
                 + 'Notas de 10:'
                 + cast (@qnota10 as varchar(max))
                 + ' ' --chr(10)
                 + 'Notas de 5:'
                 + cast (@qnota5 as varchar(max)) 
                 + ' ' --chr(10)
                 + 'Notas de 2:'
                 + cast (@qnota2 as varchar(max))
                 + ' ' --chr(10)
                 + 'Notas de 1:'
                 + cast (@qnota1 as varchar(max))
                 
    RETURN (@sretorno) -- Retorna as linhas
END;
GO
 
--alguns testes, chamada da function
 
--SELECT dbo.usf_caixa_eletro(2678);
--SELECT dbo.usf_caixa_eletro(1078); 

DECODE ZERO NO SQL SERVER

Segue um script para simular uso da função DECODE do SGBDR Oracle, nesta function, adaptei para situação em que o valor for zero,
por exemplo uma situação em que tem que dividir um valor por zero.

IF EXISTS (
            SELECT * 
              FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[usf_decode_zero]') 
               AND type IN (N'FN')
           )
   DROP FUNCTION usf_decode_zero;
  
GO

CREATE FUNCTION usf_decode_zero(@param1 float, @param2 float) RETURNS float AS
-- Nome Artefato/Programa..: usf_decode_zero.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) 
-- Data Inicio ............: 28/06/2012
-- Data Atualizacao........: 28/06/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)  verifica se o valor do primeiro parametro é igual a zero, retornando o valor do 
-- ........................:  segundo parametro2, semalhante a função decode do Oracle, porém simplificado.
-- OBS.....................: O primeiro parametro é um float, retorna o valor do segundo parametro que também é flaot, caso o primeiro 
-- ........................:  parametro seja igual a zero, caso contrário retorna o valor do primeiro parametro.
-- ........................: 
--  
BEGIN
    RETURN 
      (

   SELECT CASE WHEN @param1 = 0 THEN 
        @param2 
      ELSE 
        @param1
       END AS usf_decode_zero
   ) 

END;
GO
-- exemplo do uso da function 
SELECT dbo.usf_decode_zero (0,1200);     --Retorno: 1200
SELECT dbo.usf_decode_zero (1100,1);     --Retorno: 1100
SELECT dbo.usf_decode_zero (NULL,1);     --Retorno: NULL

-- exemplo tratando, o erro de divisão por zero, Mensagem 8134 (CLÁSSICA)
SELECT 2400/dbo.usf_decode_zero(0,1);    --Retorno: 2400
SELECT 2400/dbo.usf_decode_zero(2400,1); --Retorno: 1
SELECT 2400/dbo.usf_decode_zero(NULL,1); --Retorno: NULL

Como alterar o COLLATION de um BD no SQL Server 2005 ou 2008

Esse artigo se propõe a ensinar como alterar os collations de todos os bancos de dados de uma instancia SQL Server 2005/2008, inclusive tabelas e campos.

As vezes ou quase sempre não é possível executar o comando abaixo e mesmo que execute será necessário mudar os colations das tabelas e colunas:
--ALTER DATABASE nome_banco COLLATE Latin1_General_CI_AI;

Outro problema é que se os collations estiverem errados nos campos, dependendo da aplicação as consultas irão gerar resultados inesperados.

Figura 01 - Listando os Collations dos Bancos de Dados

Foi observado que caso o collation que queira se mudar seja igual ao do master será necessário informar outro collation diferente para que haja mudança total e depois informar novamente o collation que se deseja.

Por exemplo:

Se deseja mudar o collation para Latin1_General_CI_AI, mas o BD master está com o mesmo collation e o BD do usuário está com outro collation a mudança não será possível, então será necessário mudar o collation do BD master por exemplo: Latin1_General_CI_AS para depois mudar novamente Latin1_General_CI_AI.

Observar também que será necessário que o BD do usuário esteja na pasta padrão data do SQL Server. Exemplo de onde fica a pasta data do SQL Server 2008 com nome da instancia MSSQLSERVER (padrão) na pasta:
 C:\Arquivos de Programas\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\data.

Figura 02 - Pasta DATA padrão do MS SQL SERVER 2008 

Neste caso o procedimento para mudança do collation se faz necessário parar a instancia  do SQL SERVER,  mas para isso por segurança vamos parar todos os serviços do SQL SERVER, neste caso usei a forma nativa do mesmo,  pelo Sql Server Configuration Manager, em Iniciar->Executar digite:

SQLServerManager10.msc

Figura 03 - Parando Todos os Serviços do MS-SQL Server

Exemplo do comando a ser executado em uma instancia com MS-SQL SERVER 2008 ou em Inicar->Executar

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr -m -sMSSQLSERVER -T4022 -T3659 -q"Latin1_General_CI_AI"

Figura 04 - Comando Executado para Mudança de Collation em Iniciar->Executar

Figura 05 - Mudança de Collation sendo Processada

Figura 06 - Final do Processo de Mudança de Collation, Pressionar Y

Outro ponto a observar é que em casos que o BD do usuário tenha algum problema do tipo corrupção, mesmo que ainda não esteja no nível de SUSPECT será necessário dá um DBCC CHECKDB ('NomeBanco',repair_allow_data_loss); e em casos mais extremos, isto é paginação com problemas, usar DBCC UPDATEUSAGE (NomeBanco); depois de corrigidos os problemas no BD, repetir o processo de conversão de collation.

Figura 07 - Erro na Conversão do Collation por Motivo de BD com Problemas

Figura 08 - Executando reparação de erros no BD

Depois de concluido com sucesso a conversão do collation, iniciar o serviço do SQL SERVER pelo Sql Server Configuration Manager, em Iniciar->Executar digite:

SQLServerManager10.msc

Para verificar as mudanças efetuadas, liste os collations dos Bancos de Dados, conforme Figura 01.

Uma outra dica é desatachar os bancos de dados do ReportServer, caso não deseje também mudar o collation do mesmo,
lembrar de parar o serviço do ReportServer antes de desatachar esses bancos.

Segue script abaixo:

-- 0. Caso haja problemas de corrupção, mas ainda não está no nível de suspect com o Banco de Dados, será necessário executar os procedimentos elencados, se não pule essa parte.
ALTER DATABASE NomeBanco SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
DBCC CHECKDB ('NomeBanco',repair_allow_data_loss);
DBCC UPDATEUSAGE (NomeBanco);
ALTER DATABASE NomeBanco SET MULTI_USER WITH ROLLBACK IMMEDIATE; 

-- 1. Listar os tipos de collations Latin1_General%
SELECT * 
  FROM ::fn_helpcollations()
 WHERE name LIKE 'Latin1_General%'
  
-- 2. Listar os bancos com os seus respectivos collations (via query) 
SELECT getdate();
SELECT collation_name
     , * 
  FROM sys.databases 
 WHERE name LIKE '%nome_banco%';

-- 3. Listar os bancos com os seus respectivos collations (via store procedure) 
sp_helpdb; 

/* 
-- 4. Parar o serviço do MS-SQL SERVER em Configuration Manager ou usando o comando direto (sqlservermanager10.msc) ou usando services.msc 

-- 5. Rodar esse comando no prompt de comando (cmd.exe), ao executar esse comando o MS-SQL entrar em modo single user mode e altera todos os collations 
-- de todos os bancos, tabelas e campos, neste caso altera o "Nome_Instancia_MSSQL" e o "Nome_Collation" para o adequando nesse caso para Latin1_General_CI_AI

sqlservr -m -sNome_Instancia_MSSQL -T4022 -T3659 -q"Nome_Collation"

-- 6. Exemplo do comando a ser executado em uma instancia com MS-SQL SERVER 2005 ou em Inicar->Executar
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr -m -sMSSQL -T4022 -T3659 -q"Latin1_General_CI_AI"

-- ou se a instancia que estiver com nome diferente 
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr -m -sMSSQL.1 -T4022 -T3659 -q"Latin1_General_CI_AI"

-- ou pelo prompt do cmd já pasta binn informada acima digitar:
sqlservr -m -sMSSQL.1 -T4022 -T3659 -q"Latin1_General_CI_AI"


-- 7. Exemplo do comando a ser executado em uma instancia com MS-SQL SERVER 2008 ou em Inicar->Executar
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr -m -sMSSQLSERVER -T4022 -T3659 -q"Latin1_General_CI_AI"

-- ou pelo prompt do cmd já pasta binn informada acima digitar:

sqlservr -m -sMSSQLSERVER -T4022 -T3659 -q"Latin1_General_CI_AI"


-- 8. Quando concluir encerrar, isto é, depois que aparacer a seguinte mensagem: 
-- The default collation wa successfully changed. 
-- Recovery is complete. This is an informational message only. No user action is required.
-- Pressionar CTRL + C e depois iniciar o serviço do MS-SQL SERVER (vê item 4, inciando o serviço)

-- Caso não consiga exito executar o comando DBCC CHECKDB, pois deve ter alguma corrupção no BD.
-- 9. Depois conferir se os collations dos bancos mudaram inclusive master, tempdb, msdb (vê item 3 ou item 2 mudando o nome do banco)

*/

Vale salientar que esse procedimento, não é documentado pela Microsoft, mas a conversão funciona no MS SQL Server 2005 e 2008 sem nenhum problema.

Mais uma vez... espero ter ajudado...

Shrink MSSQL Server 2005 e 2008

Objetivo deste artigo tem com principio a orientação de como proceder redução de LOG e BD no MSSQL SERVER 2005 e 2008.

Lembro também que execução de SHRINK, impossibilita a recuperação do BD, caso ocorra alguma suspeita de corrupção do banco de dados (SUSPECT), todavia é recomendável configurar plano de manutenção, evitando assim muitas problemas.

Recomenda-se backup full antes de executar os scripts abaixo:

No MSSQL SERVER 2005 para MDF e LDF

--SHIRINK SQL SERVER 2005, formato 1, SHIRINK DE LDF e MDF 
--Alterar o [nome_banco] para seu banco de dados 
USE [nome_banco]; 
GO
BACKUP LOG [nome_banco] WITH TRUNCATE_ONLY;
GO
DBCC SHRINKDATABASE([nome_banco], 10, TRUNCATEONLY);

No MSSQL SERVER 2008 para MDF e LDF

--SHIRINK SQL SERVER 2008, formato 1, se tiver como recovery_mode full, SHIRINK DE LDF e MDF 
--Lista bancos com os modos de recuperação 
SELECT name, recovery_model, recovery_model_desc, state_desc, collation_name, user_access_desc, create_date 
FROM sys.databases;
GO

--Alterar o [nome_banco] para seu banco de dados 
USE [nome_banco]; 
GO

BACKUP LOG [nome_banco] TO DISK = 'NUL:';
GO

DBCC SHRINKDATABASE([nome_banco], 10, TRUNCATEONLY);
GO

Truncando apenas o LOG (LDF) MS SQL SERVER 2005

--SHIRINK SQL SERVER 2005, formato 2, SHIRINK DE LOG 
--Alterar o [nome_banco] para seu banco de dados 
USE [nome_banco];
GO

BACKUP LOG [nome_banco] WITH TRUNCATE_ONLY;
GO

DECLARE @log_file_logical_name sysname;
SELECT @log_file_logical_name=name FROM sys.database_files d WHERE type = 1;
PRINT @log_file_logical_name;

DBCC SHRINKFILE (@log_file_logical_name, 1);
GO

Truncando apenas o LOG (LDF) MS SQL SERVER 2008

--SHIRINK SQL SERVER 2008, formato 2, SHIRINK DE LOG 
--USANDO RECOVERY SIMPLE, isto é, desabilitando o LOG detalhado 
--Alterar o [nome_banco] para seu banco de dados 
SELECT name, recovery_model, recovery_model_desc, state_desc, collation_name, user_access_desc, create_date FROM sys.databases;
GO

USE [nome_banco];
GO
ALTER DATABASE [nome_banco] SET RECOVERY SIMPLE; 
GO

DECLARE @log_file_logical_name sysname;
SELECT @log_file_logical_name=name FROM sys.database_files d WHERE type = 1;
PRINT @log_file_logical_name;

DBCC SHRINKFILE (@log_file_logical_name, 1);
GO

-- Retornando para recovery_model para full 
ALTER DATABASE [nome_banco] SET RECOVERY FULL ; 

Espero ter ajudado!

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....

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

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

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');

Função para validar NIT PIS PASEP em MS SQL Server 2005 / 2008

Como não encontrei nada na net, para validar NIT PIS PASEP em MS SQL Server 2005 / 2008, e diante da necessidade, verificando o algoritmo para validação do NIT PIS PASEP, resolvi desenvolver uma função em T-SQL para validar esse troço.

Na verdade só testei com o MS SQL Server 2008, não tive tempo de testar no 2005, mas acho que deva funcionar em ambas. Fica por conta e risco nas dua versões.

O moído é o seguinte:

-- Nome Artefato/Programa..: usf_valida_nit_pis_pasep.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) 
-- Data Inicio ............: 19/02/2012
-- 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 numero de NIT PIS PASEP
-- 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_nit_pis_pasep]') 
               AND type IN (N'FN')
           )
 DROP FUNCTION usf_valida_nit_pis_pasep;
GO

CREATE FUNCTION usf_valida_nit_pis_pasep(@number varchar(max)) RETURNS bit AS
BEGIN
 DECLARE @weight  varchar (10)
 DECLARE @total  int
 DECLARE @c   int
 DECLARE @rest       int
 DECLARE @result  int
 DECLARE @r   bit 
 /*
 FORMATO : NNNNNNNNNND 
 ONDE:
 NNNNNNNNN - Numero do Identificador
 D   - Digito Verificador

 a) Multiplicar os 11 ultimos algarismos pelos pesos conforme descrito abaixo:
 PESOS:  3, 2, 9, 8, 7, 6, 5, 4, 3, 2
 ALGORISMO: NNNNNNNNN  
 CALCULO:  
 3 * N = X1
 2 * N = X2
 9 * N = X3
 8 * N = X4
 7 * N = X5
 6 * N = X6
 5 * N = X7
 4 * N = X8
 3 * N = X9
 2 * N = X10
 D (posicao do digito)
 */

 SET @weight = '3298765432'  -- peso estabelecido para gerar NIT PIS PASEP 
 SET @total = 0 
 SET @c  = 1 
    
 --fazendo um uma pre-validação do NIT PIS PASEP, validação 1 se é nulo
 IF @number IS NULL BEGIN
     SET @r = NULL
     RETURN (@r)  
 END 

 --fazendo um uma pre-validação do PIS PASEP, validação 2 se o tamanho é diferente de 11 digitos 
 IF LEN(@number) <> 11  BEGIN
     SET @r = 0
     RETURN (@r)
 END  --fim_se
    
 --fazendo um uma pre-validação do PIS PASEP, validação 3 se não for nulo e for mair que 11, 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 PIS PASEP, validação 4 se está vindo c/ 11 dig zerados
 IF CAST(@number AS bigint)=0 BEGIN
     SET @r = 0
     RETURN (@r)
 END  --fim_se
    
 /*
 b) Somar todos os produtos obtidos no item "a". 
 SOMA = X1 + X2 + X3 + X4 + X5 + X6 + X7 + X8 + X9 + X10
 */

 WHILE (@c<=10) 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 
 
 /*
 c) Dividir o somatório do item "b" por 11.
 DIVISAO = SOMA / 11    
 */ 
 -- dividindo, não é necessário, já vai ser feito direto no resto da divisao, apenas para acompanhar o algoritmo 
 SET @result = @total / 11 
 -- calculando resto da divisao
 SET @rest = @total % 11
 
 /*
 d) Subtrair de 11 o resto da divisao do item "c".
 RESULTADO = 11 - RESTO_DA_DIVISAO
 
 O resultado sera o digito verificador. 
 Caso o resultado da subtracao seja 10 ou 11, o digito verificador sera 0.    
 */

 SET @rest = 11 - @rest

 IF @rest = 10 OR @rest = 11 BEGIN
     SET @rest = 0 
 END --fim_se
  
 IF (@rest <> CAST (SUBSTRING(@number, 11, 1) AS INT)) BEGIN
     SET @r = 0  -- numero invalido
 END ELSE BEGIN
     SET @r = 1  -- numero valido 
 END --fim_se
    
 RETURN (@r)
    
END; 
GO

-- Fazendo chamada da function, exemplo:

SELECT dbo.usf_valida_nit_pis_pasep('12511508690'); -- retorna 1 pois é valido 
GO

-- Checando na tabela colaborador todos os NITs inválidos no campo nit, exemplo:

SELECT * 
  FROM colaborador 
 WHERE dbo.usf_valida_nit_pis_pasep(nit)=1;
GO

-- Pode se ainda implementar direto na tabela colaborador para validar o campo nit, por exemplo:

ALTER TABLE colaborador
  ADD CONSTRAINT ck_colaborador_usf_valida_nit_pis_pasep 
CHECK (dbo.usf_valida_nit_pis_pasep(nit)=1); 

GO

Mais uma vez, espero ter ajudado!
O Peregrino.