Oracle com XML? Sim veja como...

Fazendo umas pesquisas necessitei usar XML com Oracle, para compartilhar com os amigos segue a minha brincadeira, que coloco as duas formas mais comuns com elementos e com atributos:

SELECT XMLElement("Tempo"
        ,XMLAttributes(to_char(current_date,'dd/mm/yyyy') AS "Data",
                       to_char(current_date,'hh24:mi:ss') AS "Hora")
        , XMLElement("Dia",to_char(current_date,'dd'))
        , XMLElement("Mes",to_char(current_date,'mm'))
        , XMLElement("Ano",to_char(current_date,'yyyy'))
        , XMLElement("Horas",to_char(current_date,'hh24'))
        , XMLElement("Minutos",to_char(current_date,'mi'))
        , XMLElement("Segundos",to_char(current_date,'ss'))
       ) AS "XML_RESULT" FROM DUAL

Resultado da brincadeira:

<Tempo Data="16/10/2013" Hora="19:28:53">
    <Dia>16</Dia>
    <Mes>10</Mes>
    <Ano>2013</Ano>
    <Horas>19</Horas>
    <Minutos>28</Minutos>
    <Segundos>53</Segundos>
</Tempo>

Espero que isso ajude alguém, abraços

Brincando com os operadores de comparação ( ANY | SOME e ALL )

Vou apresentar desta vez operadores de comparação que muitos desconhecem, são eles: ( ANY |SOME e ALL )

DECLARE @TABELA TABLE(ID INT)
INSERT INTO @TABELA SELECT 1
              UNION SELECT 2 
              UNION SELECT 3
              UNION SELECT 4

--ANY retorna verdadeiro comparando por QUALQUER valor de um Conjunto
IF 2 < ANY(SELECT ID FROM @TABELA)
BEGIN
   PRINT '2 É MENOR QUE QUALQUER VALOR DA TABELA'
END

--SOME retorna verdadeiro comparando por ALGUM valor de um Conjunto
IF 2 < SOME(SELECT ID FROM @TABELA)
BEGIN
   PRINT '2 É MENOR QUE ALGUM VALOR DA TABELA'
END

--ALL retorna verdadeiro comparando por TODOS valores de um Conjunto
IF 0 < ALL(SELECT ID FROM @TABELA)
BEGIN
   PRINT '0 É MENOR QUE TODOS OS VALORES DA TABELA'
END

--CURIOSIDADES: EQUIVALENCIA ANY E IN

--Trazer dados de @TABELA que seja IGUAL a QUALQUER valor do Conjunto
SELECT * 
  FROM @TABELA 
 WHERE ID = ANY(SELECT 1 UNION SELECT 2 UNION SELECT 5)

--Trazer dados de @TABELA que seja valores CONTIDOS num Conjunto
SELECT * 
  FROM @TABELA 
 WHERE ID IN(SELECT 1 UNION SELECT 2 UNION SELECT 5)
/*
Resultado em ambos os casos
ID
-----------
1
2
*/
--CURIOSIDADES: EQUIVALENCIA ALL e NOT IN
--Trazer dados de @TABELA que seja diferente de TODOS valores do Conjunto
SELECT * 
  FROM @TABELA 
 WHERE ID <> ALL(SELECT 1 UNION SELECT 2 UNION SELECT 5)

--Trazer dados de @TABELA que seja valores NAO CONTIDOS em um Conjunto
SELECT *  
  FROM @TABELA 
 WHERE ID NOT IN (SELECT 1 UNION SELECT 2 UNION SELECT 5)
/*
Resultado em ambos os casos
ID
-----------
3
4
*/

SQL Server - Order By Dinâmico

Vamos a outra brincadeira com SQL-Server Em alguns sistemas existe a característica de ordenação conforme click na coluna Header, então segue um exemplo que diz respeito a este tratamento usando Banco de Dados SQL-Server
Com o trecho abaixo pode ser informado qual o número da coluna que se deseja ordenar:

DECLARE @TABELA TABLE(ID        INTEGER
                    , DESCRICAO VARCHAR(100)
                    , CADASTRO  DATETIME
                    , IDADE     INTEGER
                    , SITUACAO CHAR(1))
DECLARE @ORDERBY INTEGER

--LEGENDA DE ORDENACAO:
--[1 - ID]       
--[2 - DESCRICAO]      
--[3 - CADASTRO]      
--[4 - IDADE]         
--[5 - SITUACAO]
SET @ORDERBY = 1

INSERT INTO @TABELA VALUES(1, 'Z', '01-01-2013 00:00:00', 10, 'A')
INSERT INTO @TABELA VALUES(2, 'X', '03-03-2013 00:00:00', 40, 'I')
INSERT INTO @TABELA VALUES(3, 'Y', '02-02-2013 00:00:00', 30, 'A')

  SELECT ID, DESCRICAO, CADASTRO, IDADE, SITUACAO
    FROM @TABELA
ORDER BY CASE --ORDENAÇÃO NUMÉRICA
            WHEN @ORDERBY = 1 THEN ID            
            WHEN @ORDERBY = 3 THEN CADASTRO
            WHEN @ORDERBY = 4 THEN IDADE
         END
        ,CASE --ORDENAÇÃO NÃO NUMÉRICA
            WHEN @ORDERBY = 2 THEN DESCRICAO
            WHEN @ORDERBY = 5 THEN SITUACAO
         END

Já o próximo trecho de código resulta no mesmo resultado, mas utilizando-se do nome da coluna que se deseja ordenar:

DECLARE @TABELA TABLE(ID        INTEGER
                    , DESCRICAO VARCHAR(100)
                    , CADASTRO  DATETIME
                    , IDADE     INTEGER
                    , SITUACAO CHAR(1))
DECLARE @ORDERBY VARCHAR(20)

SET @ORDERBY = 'SITUACAO'

INSERT INTO @TABELA VALUES(1, 'Z', '01-01-2013 00:00:00', 10, 'A')
INSERT INTO @TABELA VALUES(2, 'X', '03-03-2013 00:00:00', 40, 'I')
INSERT INTO @TABELA VALUES(3, 'Y', '02-02-2013 00:00:00', 30, 'A')

  SELECT ID, DESCRICAO, CADASTRO, IDADE, SITUACAO
    FROM @TABELA
ORDER BY CASE --ORDENAÇÃO NUMÉRICA
            WHEN @ORDERBY = 'ID'        THEN ID            
            WHEN @ORDERBY = 'CADASTRO ' THEN CADASTRO
            WHEN @ORDERBY = 'IDADE'     THEN IDADE
         END
        ,CASE --ORDENAÇÃO NÃO NUMÉRICA
            WHEN @ORDERBY = 'DESCRICAO' THEN DESCRICAO
            WHEN @ORDERBY = 'SITUACAO'  THEN SITUACAO
         END

Pode-se transformar estes trechos de código em stored procedures onde tem como parâmetro de entrada a variável @ORDERBY.

Cursor em SQL-Server (Números Primos)

Lembrando que um cursor deve ser usado somente quando não é possível resolver o que se necessita com apenas SELECT, Para o nosso exemplo de Cursor, utilizo números que estão em uma "Tabela" e quero saber quais deles são primos.

DECLARE @ID        INT
DECLARE @DESCRICAO VARCHAR(100)
DECLARE @DIVISOR   INT
DECLARE @PRIMO     BIT
--Declaração de um Cursor.
DECLARE CUR CURSOR FOR SELECT 1 ID,'Um'   DESCRICAO  UNION ALL
                       SELECT 2,   'Dois'            UNION ALL
                       SELECT 3,   'Tres'            UNION ALL
                       SELECT 4,   'Quatro'          UNION ALL
                       SELECT 5,   'Cinco'           UNION ALL
                       SELECT 6,   'Seis'            UNION ALL
                       SELECT 7,   'Sete' 
--Abrindo um Cursor para uso.
OPEN CUR
--Leitura da tupla do cursor dentro das variáveis
FETCH NEXT FROM CUR INTO @ID, @DESCRICAO
--Caso exista dados no Select do Cursor
WHILE @@FETCH_STATUS = 0
BEGIN  
    SET @PRIMO   = 1     --Número é primo
    SET @DIVISOR = @ID-1 
    WHILE @DIVISOR > 1
    BEGIN
       IF @ID % @DIVISOR = 0 
       BEGIN
          SET @PRIMO = 0 --Número não é primo
          BREAK
       END
       SET @DIVISOR = @DIVISOR -1
    END
    IF @PRIMO = 0 PRINT 'O número '+@DESCRICAO+' não é primo!'
    ELSE          PRINT 'O número '+@DESCRICAO+' é primo!'
    --Leitura da tupla do cursor dentro das variáveis
    FETCH NEXT FROM CUR INTO @ID, @DESCRICAO
END
--Fechando um cursor.
CLOSE CUR            
--Liberando o cursor da memória (obrigatório)
DEALLOCATE CUR                       

Resultado:

O número Um é primo!
O número Dois é primo!
O número Tres é primo!
O número Quatro não é primo!
O número Cinco é primo!
O número Seis não é primo!
O número Sete é primo!

Preenchendo texto a direita com caracter

Neste exemplo vamos preencher um texto com algum caracter (*) a direita:

DECLARE @NUMBER VARCHAR(20)
DECLARE @COUNT  INTEGER

SET @NUMBER = 'Texto'
SET @COUNT  = 10

SELECT '1234567890' AS FORMATED UNION ALL
SELECT @NUMBER + REPLICATE('*',@COUNT - LEN(@NUMBER)) 

Resultado:

FORMATED
-----------
1234567890
Texto*****

Formatando Números com Zeros a Esquerda em SQL-Server

No exemplo abaixo vamos realizar uma formatação numérica com zeros a esquerda usando SQL-Server
Conforme a variável @COUNT informamos qual o tamanho do número:

DECLARE @NUMBER VARCHAR(20)
DECLARE @COUNT  INTEGER

SET @NUMBER = '1234'
SET @COUNT  = 8

SELECT REPLICATE('0',@COUNT - LEN(@NUMBER)) + @NUMBER AS FORMATED

Resultado:

Apenas Números em SQL-Server

Acontece de encontrarmos situações para:
>> Corrigir dados já persistidos por um sistema
>> Precisarmos validar um arquivo de carga para efetivarmos no banco de dados

Uma dessas validações é exatamente retirar dados não numéricos de um texto, segue um código para deixar apenas números a partir de um texto:

DECLARE @TEXTO VARCHAR(MAX)
SET @TEXTO = 'abc123dc4fda$%#'

DECLARE @INDEX SMALLINT
SET @INDEX = PATINDEX('%[^0-9]%', @TEXTO)
WHILE @INDEX > 0
BEGIN
   SET @TEXTO = STUFF(@TEXTO, @INDEX, 1, '')
   SET @INDEX = PATINDEX('%[^0-9]%', @TEXTO)
END
SELECT @TEXTO AS NUMERO

Resultado:

Trabalhando com Datas (Listagem de Meses para uma caixa de seleção)

Usando SQL-Server, segue código que nos retornará uma listagem dos Meses

SET LANGUAGE 'Brazilian'
DECLARE @MESES TABLE (ID VARCHAR(2), MES VARCHAR(10))
DECLARE @NUM INT
SET @NUM = 0
WHILE @NUM < 12 
BEGIN
   INSERT INTO @MESES
   SELECT REPLICATE('0',2-LEN(CAST(@NUM+1 AS VARCHAR(2))))+
          CONVERT(VARCHAR,@NUM+1)
         ,DATENAME(MONTH,DATEADD(MONTH, @NUM, 0))
   SET @NUM = @NUM + 1
END

SELECT * FROM @MESES

Resultado

Usando AJAX com ASP - [ComboBox | DropDownList | Select ]

Em ASP (Active Server Pages) o uso do AJAX (Asynchronous JavaScript e XML) serve para realizarmos processamentos em paralelo em outra página ASP, ou seja, a partir de uma página ASP usando AJAX iniciar um processamento de outra pagina ASP na qual o resultado é redirecionado para página chamadora.

Vemos na imagem abaixo:

Para o uso do AJAX que é um JavaScript Assíncrono, devemos utilizar um objeto JavaScript chamado XMLHttpRequest;
Temos no trecho de código abaixo que o JavaScript irá realizar [ xmlhttp.send(); ] uma requisição usando Assíncrona com POST de um processamento que ocorrerá na página "Processa.asp" [ xmlhttp.open("POST","Processa.asp",true); ].
Ao término do processamento [ xmlhttp.onreadystatechange...com...(xmlhttp.readyState==4 && xmlhttp.status==200) ]é recomentado realizar alguma ação com base no resultado [ xmlhttp.responseText ] do processamento.

var xmlhttp;
//Criando o Objeto AJAX
if (window.XMLHttpRequest){
    //IE7+, Firefox, Chrome, Opera, Safari
    xmlhttp=new XMLHttpRequest();
}else{
    // code for IE6, IE5
    xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}

//Quando terminar o processamento
var resultado = "";
xmlhttp.onreadystatechange = function(){
    if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
        resultado = xmlhttp.responseText;
    }
};

//Chamada do processamento Assíncrono
xmlhttp.open("POST","Processa.asp",true);
xmlhttp.send();

Antes de realizarmos o carregamento de uma combo precisamos saber o que escrever de código na nossa página Processa.asp de modo que a variável resultado contenha alguma informação;
A resposta é simples: Tudo que for escrito através de [ Response.write ];

Neste exemplo temos no html do asp a seguinte estrutura:

    Estados do Sul do Brasil: <select id='selUFSul' name='selUFSul'></select>

E em nossa página ASP Processa.asp o seguinte trecho

    //Pagina Processa.asp
    Reponse.Write "<option value=PR>Paraná</option>" 
    Reponse.Write "<option value=SC>Santa Catarina</option>" 
    Reponse.Write "<option value=RS>Rio Grande do Sul</option>" 

Em resumo quando AJAX chama a pagina Processa.asp, tudo que é armazenado em memória do buffer (Response.Write) é capturado por xmlhttp.responseText onde esse dado pode ser uma estrutura html podendo o programador popular com combo select conforme codigo abaixo:

xmlhttp.onreadystatechange = function(){
    if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
        resultado = xmlhttp.responseText;
        documento.getElementById('selUFSul').innerHTML = resultado;
    }

Enfim teremos a combo populada.

Oracle - PL-SQL - Comandos (Cursores)

Apresento como trabalhar com Cursores no Oracle

---

Exemplo cursor LOOP Simples

---

SET SERVEROUTPUT ON
DECLARE CURSOR cur IS  SELECT * FROM tb_tabela;
        linha cur%ROWTYPE;
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO linha;
        EXIT WHEN cur%NOTFOUND;       
        /* 
           comandos
           DBMS_OUTPUT.PUT_LINE(linha.campo_tabela);
        */
    END LOOP;
    CLOSE cur;
END;

---

Exemplo cursor LOOP WHILE

---

SET SERVEROUTPUT ON
DECLARE CURSOR cur IS SELECT * FROM tb_tabela;       
        linha cur%ROWTYPE;
BEGIN
    OPEN cur;
    FETCH cur INTO linha;
    WHILE cur%FOUND LOOP      
        /* 
           comandos
           DBMS_OUTPUT.PUT_LINE(linha.campo_tabela);
        */
        FETCH cur INTO linha;    
    END LOOP;
    CLOSE cur;
END;

---

Exemplo cursor LOOP FOR

---

SET SERVEROUTPUT ON
DECLARE CURSOR cur IS  SELECT * FROM tb_tabela;       
BEGIN
    FOR linha IN cur LOOP
        /* 
           comandos
           DBMS_OUTPUT.PUT_LINE(linha.campo_tabela);
        */
    END LOOP;
END;

---

Exemplo cursor LOOP FOR IMPLICITO

---

SET SERVEROUTPUT ON
BEGIN
    FOR linha IN (SELECT * FROM tb_tabela) LOOP
        /* 
           comandos
           DBMS_OUTPUT.PUT_LINE(linha.campo_tabela);
        */
    END LOOP;
END;

---

Exemplo cursor LOOP FOR (FOR UPDATE)

---

DECLARE CURSOR cur SELECT * FROM tb_tabela FOR UPDATE;
             variavel NUMBER;
BEGIN
    variavel := 1.0;
    FOR linha IN cur LOOP
        IF linha.campo_media >7 THEN
            variavel := 1.5;
        END IF;

        UPDATE tb_tabela
           SET campo_tabela = variavel
         WHERE CURRENT OF cur;
    END LOOP;
END;

---

cur%ISOPEN --- VERIFICANDO CURSOR ABERTO

---

DECLARE CURSOR cur SELECT * FROM tb_tabela 
IF cur%ISOPEN THEN
 CLOSE cur;
END IF;
OPEN cur;

---

SQL%FOUND oposto SQL%NOTFOUND

---

SET SERVEROUTPUT ON
DELETE FROM tb_tabela WHERE condicao_verdadeira;
 
IF SQL%FOUND THEN  -- Sucesso na Cláusula DELETE
       DBMS_OUTPUT.PUT_LINE( 'Linhas afetadas na instrução DELETE: '|| TO_CHAR(SQL%ROWCOUNT));
END IF;

---

tb_tabela.campo_tabela%TYPE
Declarando variável do mesmo tipo de uma coluna de uma tabela

---

DECLARE variavelCampo tb_tabela.campo_tabela%TYPE; 

---

tb_tabela%ROWTYPE
Declarando uma variável com estrutura de uma tupla de uma tabela

---

DECLARE linha tb_tabela%ROWTYPE; 

---

Criando um Tipo de dados

---

DECLARE 
    TYPE novoTipo IS TABLE OF tb_tabela.campo_tabela%TYPE;
    variavelNovoTipo  novoTipo;
    CURSOR cur IS  
        SELECT campo_tabela
          FROM tb_tabela 
         WHERE condicao_verdadeira;

BEGIN
    OPEN cur;
    FETCH cur BULK COLLECT INTO variavelNovoTipo;
    CLOSE cur;

    FOR i IN variavelNovoTipo.FIRST .. variavelNovoTipo.LAST 
    LOOP
        DBMS_OUTPUT.PUT_LINE( variavelNovoTipo(i) );
    END LOOP;
END;  

Oracle - PL-SQL - Comandos (Laços)

Abaixo os Laços possíveis que podemos usar no Oracle

---

LOOP FINITO CONDICIONAL

---

LOOP
    /*
        comandos
    */
    EXIT WHEN condicao_verdadeira
END LOOP;

---

LOOP FOR N VEZES

---

FOR i IN 1..N LOOP
    /*
        comandos
        quando i for igual a N sai do loop
    */    
END LOOP;

---

LOOP FOR N VEZES INVERTIDO

---

FOR i IN REVERSE 1..N LOOP
    /*
        comandos
        quando i for igual a 1 sai do loop
    */    
END LOOP;

---

LOOP WHILE CONDICIONAL

---

DECLARE i NUMBER := 0;
DECLARE n NUMBER := 10;
WHILE i < n LOOP
    /*
        comandos
        quando i for igual a 'n' sai do loop
    */    
    i := i + 1;
END LOOP;

Javascript: Validar Data no Formato DD/MM/YYYY

Sempre que ao pesquisar sobre uma validação de data encontrava muito código para pouco resultado, e após algum estudo resolvi criar uma validação feita por mim, utilizei um conceito muito simples: se após converter o valor para o tipo Data e após a conversão a data ainda for a mesma, quer dizer que a conversão foi um sucesso, então sabemos que a data é válida.

//-----------------------------------------------------------------
// Entrada DD/MM/AAAA
//-----------------------------------------------------------------
function fctValidaData(obj)
{
    var data = obj.value;
    var dia = data.substring(0,2)
    var mes = data.substring(3,5)
    var ano = data.substring(6,10)

    //Criando um objeto Date usando os valores ano, mes e dia.
    var novaData = new Date(ano,(mes-1),dia);

    var mesmoDia = parseInt(dia,10) == parseInt(novaData.getDate());
    var mesmoMes = parseInt(mes,10) == parseInt(novaData.getMonth())+1;
    var mesmoAno = parseInt(ano) == parseInt(novaData.getFullYear());

    if (!((mesmoDia) && (mesmoMes) && (mesmoAno)))
    {
        alert('Data informada é inválida!');   
        obj.focus();    
        return false;
    }  
    return true;
}

Segue abaixo um exemplo de como utilizar a function:

<body>

Data: 
<input type='text' id='txtDt' name='txtDt' onblur='fctValidaData(this);'/>

</body>

Por que um Datawarehouse?

A pedido do meu amigo Serginho [Sheep] estou escrevendo este post sobre Datawarehouse.

Este post não tem o foco de dizer como construir um Datawarehouse mas dizer o que é e para que serve. Então vou contar uma historinha.....(risos)

Datawarehouse como o próprio nome diz, do inglês "Armazém de Dados" é um repositório que contém dados para finalidades exclusivas de análise de dados para facilitar em tomadas de decisões em um ambiente corporativo, também conhecido pelas siglas DW, DWH ou EDW (Enterprise Datawarehouse).

Em grandes ambientes corporativos que tem um sistema de informação bem definido, (nos moldes tradicionais, dentro dele possui relatórios de finalidade de gerenciamento), muitas vezes seus relatórios não são suficientemente ideais para que diretores realize a tomada de decisões na empresa em que trabalha, e nestes sistemas quando criados, podemos considerar que um relatório não "nasce" com o sistema, pois, são de tempos em tempos solicitados pelos diretores.

Pensando que a cada dia um novo relatório "nasce", e que um relatório quando solicitado, pode levar horas ou até mesmo dias para ser visualizado pelo gestor, além de possuir um custo por passar por um processo de desenvolvimento, surgiu a necessidade de uma solução que permitisse aos diretores e gestores uma tomada de decisão mais ágil e evitar esse tempo "perdido" até ter a informação, assim surgiu o Datawarehouse.

Um DW é um banco de dados que deve ser construído de maneira que facilite a visualização de dados cruzados, o mesmo normalmente é "alimentado" por meio de dados já persistidos em outro banco de dados (normalmente um relacional). Não devemos pensar que o mesmo será a solução para todos os relatórios possíveis do sistema, suas possibilidades são permitidas conforme uma análise da necessidade e essência do sistema, um trabalho conjunto entre Administradores de Banco de Dados e Analistas de Sistemas pode ser importante para definir bem o foco das possíveis necessidades do cliente, porém o responsável por construir e definir as regras para criação do Datawarehouse é o DBA (DataBase Administrator).

O mais importante é saber que o DW não veio para substituir todos os relatórios de um sistema, em um DW serão armazenadas apenas informações históricas, são dados que não podem sofrer alterações, pois normalmente os gestores tomam decisões com base em informações passadas, então como exemplo: pode se adotar que no sistema serão visíveis dados apenas de até 1 anos atraz, sendo os demais anos anteriores os dados visiveis apenas em um DW.
Exitem ferramentas gerenciais de BI (Business Inteligence) que permitem o acesso de maneira eficiente de um DW.

Postgresql - Paginação com Filtros e Ordenação

Para o caso de precisarmos criar uma consulta que nos retorne os dados paginados com filtros e ordenados
Veja no exemplo abaixo como é facil usando Postgresql

------------------------------------------------------------------
SELECT Z.* FROM 
  (      SELECT  1 AS ID, 'UM'     AS DESCRICAO    ,12 AS NUMERO
   UNION SELECT  2 AS ID, 'DOIS'   AS DESCRICAO    ,15 AS NUMERO   
   UNION SELECT  3 AS ID, 'TRÊS'   AS DESCRICAO    ,26 AS NUMERO   
   UNION SELECT  4 AS ID, 'QUATRO' AS DESCRICAO    ,20 AS NUMERO  
   UNION SELECT  5 AS ID, 'CINCO'  AS DESCRICAO    ,23 AS NUMERO   
   UNION SELECT  6 AS ID, 'SEIS'   AS DESCRICAO    ,32 AS NUMERO   
   UNION SELECT  7 AS ID, 'SETE'   AS DESCRICAO    ,14 AS NUMERO  
   UNION SELECT  8 AS ID, 'OITO'   AS DESCRICAO    ,27 AS NUMERO   
   UNION SELECT  9 AS ID, 'NOVE'   AS DESCRICAO    ,11 AS NUMERO   
   UNION SELECT 10 AS ID, 'DEZ'    AS DESCRICAO    ,21 AS NUMERO   
  ) Z
           WHERE UPPER(Z.DESCRICAO) LIKE UPPER('%%') --filtros diversos
        ORDER BY Z.ID -- Ordenação
           LIMIT 3  -- Quantidade de registros por página
          OFFSET (3 - 1) * 3 
               --(Numero da Pagina -1) * Quantidade registros por página

------------------------------------------------------------------

SQL Server 2000 - Paginação com Filtros e Ordenação

Bom, em se tratando de listagem de dados, algumas vezes encontramos a necessidade de fazer uma query que nos retorne muitos registros, neste momento é hora de pensar em dados paginados, onde é apresentado alguns registros a cada página, mas em muitos casos é necessário deixar disponível a ordenação a cargo do usuário, ao clicar no cabeçalho executa ordenação pela mesma.
Segue abaixo uma solução para criar uma Stored Procedure, que a consulta retorne apenas o que queremos evitando muitos registros ocultos ou depender de componentes externos, além de poder aplicar filtros e ordenação.

-----------------------------------------------------------------------
--Populando uma tabela para o exemplo
-----------------------------------------------------------------------
DECLARE @TABELA TABLE(
                      ID        INT
                     ,DESCRICAO VARCHAR(100)
                     ,NUMERO    INT
                     )
INSERT INTO @TABELA
                          SELECT  1, 'UM'    ,12
                    UNION SELECT  2, 'DOIS'  ,15
                    UNION SELECT  3, 'TRÊS'  ,26
                    UNION SELECT  4, 'QUATRO',20
                    UNION SELECT  5, 'CINCO' ,23
                    UNION SELECT  6, 'SEIS'  ,32
                    UNION SELECT  7, 'SETE'  ,14
                    UNION SELECT  8, 'OITO'  ,27
                    UNION SELECT  9, 'NOVE'  ,11
                    UNION SELECT 10, 'DEZ'   ,21

-----------------------------------------------------------------------
--Parâmetros da StoredProcedure
-----------------------------------------------------------------------
--Filtros
DECLARE @Filtro_ID        INT          --ID EXATO
DECLARE @Filtro_DESCRICAO VARCHAR(100) --PARTE DA DESCRICAO
DECLARE @Filtro_NUMERO    INT          --NUMERO EXATO

--Ordenação
DECLARE @Ordenacao      INT          --ATRAVEZ DO INDICE INFORMADO

--Paginação
DECLARE @NrPagina       INT          --INFORMA O NUMERO DA PÁGINA 
DECLARE @PorPagina      INT          --INFORMA REGISTROS POR PÁGINA
DECLARE @TemProxima     CHAR(1)      --RETORNA 'S' COM PROXIMA PÁGINA 
                                     --     OU 'N' SEM PROXIMA PÁGINA
-----------------------------------------------------------------------
--Valores para exemplificar este seriam os dados de entrada da Procedure
SET @Filtro_ID        = NULL
SET @Filtro_DESCRICAO = NULL
SET @Filtro_NUMERO    = NULL
SET @NrPagina         = 1
SET @PorPagina        = 3
SET @Ordenacao        = NULL
-----------------------------------------------------------------------

DECLARE @IndexInicial INT 
SET @IndexInicial = (@NrPagina * @PorPagina ) - @PorPagina 

--CRIANDO ESTRUTURA IGUAL TABELA MAS COM COLUNA ROWID ORDENADO E FILTRADO
DECLARE @TABELA_PRONTA TABLE(
                               ROWID     INT IDENTITY(1,1)
                              , ID        INT
                              , DESCRICAO VARCHAR(100)
                              , NUMERO INT
                              )

--INSERINDO DADOS PRONTOS PARA SEREM APRESENTADOS
INSERT INTO @TABELA_PRONTA (ID, DESCRICAO, NUMERO)
   SELECT E.ID ,E.DESCRICAO ,E.NUMERO      
     FROM @TABELA E
    WHERE ( E.ID = @ID OR @ID IS NULL )
      AND ( UPPER(E.DESCRICAO) LIKE '%'+UPPER(@DESCRICAO)+'%' 
            OR @DESCRICAO IS NULL )
      AND (E.NUMERO = @NUMERO OR @NUMERO IS NULL)
 ORDER BY CASE --ORDENACOES INTEIRAS
             WHEN @Ordenacao = 1 THEN E.ID
             WHEN @Ordenacao = 3 THEN E.NUMERO
          END,
          CASE --ORDENACOES VARCHAR
             WHEN @Ordenacao = 2 THEN E.DESCRICAO
          END


--PAGINAÇÃO
SET ROWCOUNT @PorPagina

SELECT E.ROWID
      ,E.ID
      ,E.DESCRICAO
      ,E.NUMERO
  FROM @TABELA_PRONTA E
 WHERE ROWID > @IndexInicial

SET ROWCOUNT 0

--Informando se existe próxima página
SELECT @TemProxima = CASE 
                        WHEN COUNT(ID) > (@NrPagina * @PorPagina ) THEN 
                           'S'
                        ELSE 
                           'N'
                     END
  FROM @TABELA

-----------------------------------------------------------------------

Até o próximo post.

SQL Server 2000 - Stored Procedure com parâmetro para IN

Em alguns momentos em nossa jornada de trabalho podemos precisar criar uma Stored Procedure no qual precisamos passar como parâmetro de entrada um conjunto de dados (para a clausula IN da Query) com a finalidade de realizar o filtro dos dados. O trecho de código abaixo foi construido para ser executado sem a necessidade de criação de objetos, mantendo a lógica, basta criar a Stored Procedure de acordo com a necessidade e usando as tabelas reais.
Basta copiar, colar e executar.

-----------------------------------------------------------------------
--Criamos uma tabela populada para montar nosso exemplo 
-----------------------------------------------------------------------
DECLARE @TABELA TABLE(ID INT, DESCRICAO VARCHAR(100))
INSERT INTO @TABELA
      SELECT  1, 'UM'       UNION SELECT  2, 'DOIS'     
UNION SELECT  3, 'TRÊS'     UNION SELECT  4, 'QUATRO'   
UNION SELECT  5, 'CINCO'    UNION SELECT  6, 'SEIS'     
UNION SELECT  7, 'SETE'     UNION SELECT  8, 'OITO'     
UNION SELECT  9, 'NOVE'     UNION SELECT 10, 'DEZ'
-----------------------------------------------------------------------

DECLARE @SEPARADOR CHAR(1)           --Parâmetro de Entrada
DECLARE @DADOS     VARCHAR(8000)     --Parâmetro de Entrada

DECLARE @VALOR     VARCHAR(8000)
DECLARE @TABELA_IN TABLE (VALOR VARCHAR(8000))

--Exemplo de dados nos parâmetros de entrada
SET @SEPARADOR = ','
SET @DADOS     = 'UM,TRÊS,CINCO,SETE,NOVE'


SET @DADOS     = @DADOS + @SEPARADOR

--Percorrendo @DADOS para gravar os Itens na tabela temporária buscando
--por meio do @SEPARADOR.
WHILE LEN(@DADOS) > 0
BEGIN
   SET @VALOR = 
        LTRIM(SUBSTRING(@DADOS, 1, CHARINDEX(@SEPARADOR, @DADOS) - 1))
   SET @DADOS = 
        SUBSTRING(@DADOS, CHARINDEX(@SEPARADOR, @DADOS) + 1, LEN(@DADOS))

   INSERT INTO @TABELA_IN (VALOR) VALUES (@VALOR)
END

--Realizando a consulta desejada
SELECT T.ID, T.DESCRICAO
  FROM @TABELA T  
 WHERE T.DESCRICAO IN (SELECT VALOR FROM @TABELA_IN)

-----------------------------------------------------------------------