BlogDoSql

Armazenando o select de uma Stored Procedure SQL

noreply@blogger.com (Ronderson) — Thu, 23 Dec 2010 03:24:00 +0000

Para armazenar o select de uma stored procedure, no sql 2005 eh um pouco diferente.
precisa seguir alguns passos:

- precisa ter um linked server configurado.
- chamar a rotina que retornara o conteudo da proc.

configurando o linked server:

exec sp_addlinkedserver
@server = '[nome que vc dara ao link]',
@srvproduct = '',
@provider = 'SQLNCLI',--FICA ASSIM MESMO
@provstr = 'DRIVER={SQLServer};SERVER=[IP DO SEU SERVIDOR ONDE ESTA A SUA BASE];UID=[LOGIN];PWD=[SENHA];'

feito isso teremos o link configurado.
agora basta chamar a rotina assim:

select * from openquery([nome dado ao link],
'execute [Teste_RetornoProc])

com isso pode-se inserir o retorno em qualquer tabela, tanto temporaria ou variavel, ou mesmo tabela fisica.

Armazenando o retorno de uma Stored Procedure SQL

noreply@blogger.com (Ronderson) — Thu, 23 Dec 2010 00:12:00 +0000

Esse eu perdi um tempinho procurando pois nao sabia como funcionava, mas achei um tutorial da microsoft e fiquei besta da facilidade:

Ex.
--proc exemplo
create procedure Teste_RetornaStatus
@contratoId as int
as begin
   select contratoStatus
   from contrato
   where contratoId = @contratoId
end

--executando a proc
declare @status as int

--armazenando o retorno da procedure (simples)
execute @status = Teste_RetornaStatus @contratoId = 12

pronto , o retorno esta na variavel @status.

Cláusula OUTPUT

noreply@blogger.com (Ronderson) — Tue, 21 Dec 2010 00:31:00 +0000

As instruções INSERT, DELETE e UPDATE ganharam uma nova clausula, OUTPUT .

Ex:
--declarando uma tabela que ira receber os dados
DECLARE @Deletados AS TABLE (materia INT, nome_artita VARCHAR(30))

--executando o codigo e abastacendo a tabela
DELETE aluna OUTPUT
DELETED.materia,
DELETED.nome_artista
INTO @Deletados
WHERE materia = 6

PIVOT - transformando linhas em colunas

noreply@blogger.com (Ronderson) — Tue, 21 Dec 2010 00:29:00 +0000

A função PIVOT transforma o ajuste de linhas para colunas.

Ex: --TRANSFORMANDO LINHAS EM COLUNAS NO SQL SERVER 2000

select  Produto,
           sum(case Mes when 1 then Valor else 0 end) as Jan,
           sum(case Mes when 2 then Valor else 0 end) as Fev,
           sum(case Mes when 3 then Valor else 0 end) as Mar
from foo
group by Produto

--TRANSFORMANDO LINHAS EM COLUNAS NO SQL SERVER 2005

select Produto,[1] as Jan, [2] as Fev,[3] as Mar

from

(
   select Valor,Mes,Produto
   from foo
) p PIVOT
(sum(Valor) for Mes in ([1],[2],[3]) ) as foo

TOP

noreply@blogger.com (Ronderson) — Tue, 21 Dec 2010 00:27:00 +0000

O Top foi inserido no SQL 7.0, porém esta clausula no SQL 2005 traz algumas alterações. Permite, por exemplo, o número da percentagem a ser retornada em um SELECT. E pode também ser utilizada também para INSERT, UPDATE e DELETE.

Ex:
-- Retorna Linha 1 e Linha 2
SELECT TOP(2) * FROM alunos

--Atualiza Linha 1 e Linha 2 para 'S'
UPDATE TOP(2) alunos
SET Col1='S’

--Apaga as 2 primeiras linhas onde 'S' foi apagado
DELETE TOP(2) alunos

NTILE

noreply@blogger.com (Ronderson) — Tue, 21 Dec 2010 00:26:00 +0000

Esta função nos permite separar o resultado de uma Query em um determinado número de grupos de acordo com uma ordem.
Ex:
SELECT   Vendedor,
                 Produto,
                 Quantidade,
                 ROW_NUMBER() OVER(ORDER BY Quantidade DESC) AS Numero,
                 NTILE(3) OVER(ORDER BY Quantidade DESC) as Grupo
FROM   Analise_Vendas
ORDER BY Quantidade DESC

RANK() e DENSE_RANK()

noreply@blogger.com (Ronderson) — Tue, 21 Dec 2010 00:25:00 +0000

RANK e DENSE_RANK atribuem o mesmo valor para as colunas que possuem o mesmo valor dentro da ordem qe foi estabelecida.

Ex:
SELECT    Vendedor, Produto, Quantidade,
                  ROW_NUMBER() OVER(ORDER BY Quantidade ASC) AS Numero,
                  RANK() OVER(ORDER BY Quantidade asc) AS Rank,
                  DENSE_RANK() OVER(ORDER BY Quantidade ASC) AS DenseRank
FROM Analise_Vendas ORDER BY Quantidade asc

Paginação

noreply@blogger.com (Ronderson) — Mon, 20 Dec 2010 04:16:00 +0000

PAGINAÇÃO - Uma outra ídéia para utlizar esta função seria para criar uma páginação para nossa Query, podendo utiliza-lá em alguma página na Web.

Ex: DECLARE
@NumeroDaPagina AS INT,
@TamanhoDaPagina AS INT

SET @NumeroDaPagina = 2
SET @TamanhoDaPagina = 5

SELECT
*
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY Quantidade DESC ) AS Numero,
                 Vendedor,
                  Produto ,
                 Quantidade,
                 Telefonemas
FROM Analise_Vendas) AS V WHERE Numero

BETWEEN (@NumeroDaPagina-1)* @TamanhoDaPagina+1 AND

@NumeroDaPagina*@TamanhoDaPagina ORDER BY Quantidade DESC,

Telefonemas

Partition by

noreply@blogger.com (Ronderson) — Mon, 20 Dec 2010 04:13:00 +0000

PARTITION BY - Outra opção da sintaxe básica que se chama PARTITION BY, que

funciona mais ou menos como o group by, definindo por qual

critério nosso ROW_NUMBER deverá ser ressetado.

Ex: select ROW_NUMBER() over ( PARTITION BY Produto ORDER BY Quantidade Desc ) as Numero, Vendedor, Produto, Quantidade from Analise_Vendas

Row_Number

noreply@blogger.com (Ronderson) — Mon, 20 Dec 2010 04:12:00 +0000

Row_number  Utilizando a função row_number podemos não só numerar as linhas

de uma tabela como também fazer essa numeração em uma ordem

específica, diferente da ordem na qual as linhas serão exibidas.

Ex: -- no SQL 2005 select ROW_NUMBER() over ( ORDER BY Quantidade desc ) as Numero, Vendedor, Produto, Quantidade, Telefonemas from Analise_Vendas

UNION ALL vs UNION e ordenação

noreply@blogger.com (Ronderson) — Fri, 17 Dec 2010 17:41:00 +0000

Se você sabe que não pode haver valores duplicados entre duas tabelas, então sempre use UNION ALL. Ele salva o banco de dados de realizar diversas triagens para ordenação dos seus dados.

Seu DBA não estará satisfeito com você se usar UNION desnecessariamente. É uma característica padrão do UNION realizar ordenação, mesmo sem a existência da opção de ORDER BY.

Otimizações SQL

noreply@blogger.com (Ronderson) — Fri, 17 Dec 2010 00:44:00 +0000

Algumas dicas para otimizações em instruções SQL (em alguns casos o servidor de banco de dados pode não suportar algumas instruções ou cláusulas).

1) Todas as vezes que for utilizar um SQL que possua condições de OR, é mais aconselhável e mais rápido utilizar IN, como no exemplo:

AO INVÉS DE

select * from projint where sit_projint = ‘AI’ or sit_projint = ‘EL’

DIGITE

select * from projint where sit_projint IN (‘AI’,‘EL’);

2) Quando existem duas ou mais condições AND juntas, especifique primeiro sempre a que possui o maior limite de ocorrências

AO INVÉS DE

select count(*) from pessoa where sit_pessoa = 11 AND cod_munic > 1100155

USE

select count(*) from pessoa where cod_munic > 1100155 AND sit_pessoa = 11

3) Quando existem duas ou mais condições OR juntas, especifique primeiro sempre a que possui o maior limite de ocorrências

AO INVÉS DE

select count(*) from pessoa where cod_munic > 1100155 OR sit_pessoa = 11

DIGITE

select count(*) from pessoa where sit_pessoa = 11 OR cod_munic > 1100155

4) Tenha cuidado com o sinal de <>

AO INVÉS DE

select count(*) from pessoawhere cod_munic < > 1100155

DIGITE

select count(*) from pessoawhere cod_munic < 1100155 OR cod_munic > 1100155

Transferindo registros de uma tabela para outra

noreply@blogger.com (Ronderson) — Fri, 17 Dec 2010 00:28:00 +0000

para inserir dados de uma tabela para outra, seria assim:
INSERT INTO table1 (nome) SELECT nome FROM table2;

Criação de usuários

noreply@blogger.com (Ronderson) — Fri, 17 Dec 2010 00:26:00 +0000

GRANT ALL PRIVILEGES ON meuBD.* TO ronderson@localhost

IDENTIFIED BY '123mudar' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Descrição: atribui todos os privilégios à todas as tabelas do banco de dadosmeuBD ao usuário ronderson, a partir da máquina localhost, cuja senha é 123mudar. O comando FLUSH PRIVILEGES atualiza as novas alterações no daemon do MySQL. Caso o usuário joao não existe, um novo usuário será criado.

Mantém somente Números

noreply@blogger.com (Ronderson) — Thu, 16 Dec 2010 19:29:00 +0000

Pensando na necessidade de limpar os cadastros de telefone, existe uma forma de retirar tudo que nao seja numero de um telefone, mantendo somente oque lhe interessa realmente. Abaixo esta uma function que realiza esse processo:

USE [purkinje]
GO
/****** Object: UserDefinedFunction [dbo].[FVALIDA_NUMEROS] Script Date: 12/16/2010 17:27:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[FVALIDA_NUMEROS] (@PALAVRA VARCHAR (1000)) RETURNS VARCHAR (1000) AS
BEGIN
DECLARE
@RESULTADO VARCHAR (1000),
@LETRA VARCHAR(1),
@QTD_PALAVRA INTEGER,
@CONT INTEGER
SET @CONT = 0
SET @QTD_PALAVRA = LEN(@PALAVRA)
SET @RESULTADO = ''
WHILE @CONT < @QTD_PALAVRA
BEGIN
SET @CONT = @CONT + 1
SET @LETRA = SUBSTRING(@PALAVRA,@CONT,1)
IF @LETRA IN ('0','1','2','3','4','5','6','7','8','9' )
BEGIN
SET @RESULTADO = @RESULTADO + @LETRA
END
END
RETURN @RESULTADO
END

Function Valida_CPF

noreply@blogger.com (Ronderson) — Thu, 16 Dec 2010 19:24:00 +0000

Hoje em dia há necessidade de efetuar a validação do CPF não só na aplicação mas também na base de dados. Pensando nisso abaixo segue o código para validar o CPF através de uma Function:

USE [Banco de Dados]
GO
/****** Object: UserDefinedFunction [dbo].[FN_VALIDA_CPF] Script Date: 12/16/2010 17:22:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[FN_VALIDA_CPF]
(
@CPF VARCHAR(14)
)
RETURNS BIT AS

BEGIN

DECLARE @NEW_CPF VARCHAR(14)
DECLARE @CPF2 VARCHAR(14)
DECLARE @X INT
DECLARE @FN_VALIDA_CPF BIT

SET @NEW_CPF = ''
SET @X = 0
SET @FN_VALIDA_CPF = 1

SELECT @FN_VALIDA_CPF = (CASE RTRIM(LTRIM(@CPF))
WHEN '00000000000' THEN 0
WHEN '11111111111' THEN 0
WHEN '22222222222' THEN 0
WHEN '33333333333' THEN 0
WHEN '44444444444' THEN 0
WHEN '55555555555' THEN 0
WHEN '66666666666' THEN 0
WHEN '77777777777' THEN 0
WHEN '88888888888' THEN 0
WHEN '99999999999' THEN 0
END)

IF @FN_VALIDA_CPF = 0
RETURN @FN_VALIDA_CPF

SET @FN_VALIDA_CPF = 0

DECLARE @SOMA INT
DECLARE @DV1 INT
DECLARE @DV2 INT
DECLARE @DVS CHAR(2)

SET @SOMA = 0
SET @DV1=0
SET @DV2=0
SET @DVS = ''

WHILE (@X=48 and ASCII(SUBSTRING(@CPF,@X,1))<=57
SET @NEW_CPF=@NEW_CPF + SUBSTRING(@CPF,@X,1)
END

IF ISNUMERIC(@NEW_CPF)=0 RETURN 0

SET @CPF = @NEW_CPF

SET @X=0
WHILE (@X<9) BEGIN
SET @X = @X + 1
SET @SOMA = @SOMA + (11-@X) * (CONVERT(int,SUBSTRING(@CPF,@X,1)))
END

SET @DV1 = 11 - (@SOMA % 11)
IF @DV1 IN (10,11)
SET @DV1=0
SET @CPF2 = @CPF + CONVERT(VARCHAR,@DV1)
SET @SOMA = 0

SET @X = 0
WHILE (@X <=9) BEGIN
SET @X = @X + 1
SET @SOMA = @SOMA + (12-@X)*(CONVERT(INT,SUBSTRING(@CPF2,@X,1)))
SET @DV2 = 11 - (@SOMA % 11)
END

IF @DV2 IN (10,11)
SET @DV2=0

SET @DVS = CONVERT(VARCHAR,@DV1) + CONVERT(VARCHAR,@DV2)

IF @DVS = RIGHT(@CPF,2)
SET @FN_VALIDA_CPF = 1
ELSE
SET @FN_VALIDA_CPF = 0

RETURN (@FN_VALIDA_CPF)

END

Início

noreply@blogger.com (Ronderson) — Thu, 16 Dec 2010 01:43:00 +0000

Em breve teremos varias dicas e explicacoes sobre diversos comandos e novidades do mundo SQL