Short Scripts – Julho – 2014

Boa Tarde Comunidade!

Esta chegando ao final mais uma Copa do Mundo, poxa vida passou rápido demais, mas foi óitmo. Mas a vida continua e se Deus permitir próximas Copas do Mundo virão e teremos mais sucesso e felicidades.

Desta forma, e mantendo a tradição vou compartilhar com vocês alguns do Short Scripts que utilizei nos últimos dias e me ajudarem muito.

Neste relação você vai encontrar exemplos e códigos que envolvem os seguintes recursos, comandos e funcionalidades:

  • Bloco Try…Catch;
  • Claúsula Output;
  • Comando Merge;
  • Constraints Default;
  • Função para Formatação de Valores;
  • Função Unpivot;
  • Script para Insert de Dados;
  • Stored Procedure;
  • Tipo de Dados UniqueIdentifier; e
  • XML com Versionamento e Enconding.

Segue abaixo os Short Scripts:

1– Exemplo – Stored Procedure + Comando Merge + Output:

— Criando a Tabela Producao —
Create Table Producao
(ControleProducao Int Identity(1,1) Primary Key,
OrdemProducao Varchar(20) Not Null,
DataProducao DateTime Not Null,
Quantidade Int Not Null)
On [Primary]
Go

— Criando a Stored Procedure P_FindProducao —
Create Procedure P_FindProducao @OrdemProducao VarChar(20), @DataProducao DateTime
As
Begin
Set NoCount On;

Merge Producao As Target
Using (Select @OrdemProducao, @DataProducao) As Source (OrdemProducao, DataProducao)
On (Target.OrdemProducao = Source.OrdemProducao
And Target.DataProducao = Source.DataProducao)
When Matched Then
Update Set Quantidade = Quantidade + 1, DataProducao = GetDate()
When Not Matched Then
Insert (OrdemProducao, DataProducao, Quantidade)
Values(Source.OrdemProducao, Source.DataProducao, 1)
OUTPUT deleted.*, $action, inserted.*;

End
Go

Select * from Producao

Insert Into Producao (OrdemProducao, DataProducao, Quantidade)
Values (1,GetDate(),1),
(2,GetDate(),1),
(3,GetDate(),1)
Exec P_FindProducao 1, ‘2014-07-08 10:06:50.297’

 

— Criando a Tabela Producao —
Create Table Producao
(OrdemProducao Varchar(20) Not Null Primary Key Clustered,
DataProducao DateTime Not Null,
Quantidade Int Not Null)
On [Primary]
Go

2 – Exemplo – Stored Procedure + Comando Merge + Output + Try….Catch:

— Criando a Stored Procedure P_FindProducao —
Create Procedure P_FindProducao @OrdemProducao VarChar(20), @DataProducao DateTime
As
Begin
Set NoCount On;

Begin Try

Merge Producao As Target
Using (Select @OrdemProducao, @DataProducao) As Source (OrdemProducao, DataProducao)
On (Target.OrdemProducao = Source.OrdemProducao
And Target.DataProducao = Source.DataProducao)
When Matched Then
Update Set Quantidade = Quantidade + 1, DataProducao = GetDate()
When Not Matched Then
Insert (OrdemProducao, DataProducao, Quantidade)
Values(Source.OrdemProducao, Source.DataProducao, 1);

End Try

Begin Catch

SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_LINE() AS ErrorLine;

End Catch
End
Go

Select * from Producao

Insert Into Producao (OrdemProducao, DataProducao, Quantidade)
Values (1,GetDate(),1),
(2,GetDate(),1),
(3,GetDate(),1)
Exec P_FindProducao 3, ‘2014-07-08 10:26:25.250’

3 – Exemplo – Utilizando – UniqueIdentifier + Default +NewSequentialID:

Create TAble T1
(Codigo UniqueIdentifier Primary Key Default NEWSEQUENTIALID(),
Descricao varchar(100))
Insert into t1 (Descricao)
Values (CONVERT(Varchar(100), NewID()))
Go 10000

4 – Exemplo – Stored Procedure – Script de Insert:

if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_CreateDataLoadScript]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[sp_CreateDataLoadScript]
GO

Create Procedure sp_CreateDataLoadScript
@TblName varchar(128)
as
/*
exec sp_CreateDataLoadScript ‘MyTable’
*/
create table #a (id int identity (1,1), ColType int, ColName varchar(128))

insert #a (ColType, ColName)
select case when DATA_TYPE like ‘%char%’ then 1 else 0 end ,
COLUMN_NAME
from information_schema.columns
where TABLE_NAME = @TblName
order by ORDINAL_POSITION

if not exists (select * from #a)
begin
raiserror(‘No columns found for table %s’, 16,-1, @TblName)
return
end

declare @id int ,
@maxid int ,
@cmd1 varchar(7000) ,
@cmd2 varchar(7000)

select @id = 0 ,
@maxid = max(id)
from #a

select @cmd1 = ‘select ” insert ‘ + @TblName + ‘ ( ‘
select @cmd2 = ‘ + ” select ” + ‘
while @id < @maxid
begin
select @id = min(id) from #a where id > @id

select @cmd1 = @cmd1 + ColName + ‘,’
from #a
where id = @id

select @cmd2 = @cmd2
+ ‘ case when ‘ + ColName + ‘ is null ‘
+ ‘ then ”null” ‘
+ ‘ else ‘
+   case when ColType = 1 then  ””””’ + ‘ + ColName + ‘ + ””””’ else ‘convert(varchar(20),’ + ColName + ‘)’ end
+ ‘ end + ”,” + ‘
from #a
where id = @id
end
select @cmd1 = left(@cmd1,len(@cmd1)-1) + ‘ ) ” ‘
select @cmd2 = left(@cmd2,len(@cmd2)-8) + ‘ from ‘ + @tblName

select ‘/*’ + @cmd1 + @cmd2 + ‘*/’

exec (@cmd1 + @cmd2)
drop table #a

Go

5 – Exemplo – Criando – Função – Formatação de Valores:

CREATE FUNCTION FormataVT (@Valor float)
Returns varchar(30) as
Begin
Return Replace(Replace(Replace(Convert(Varchar, Convert(Money, @Valor), 1), ‘.’, ‘x’),
‘,’, ‘.’),
‘x’, ‘,’);
End;
Go
Declare @Valores Table (Valor_Total float);
INSERT into @Valores values (1032.5), (1542.24);

SELECT dbo.FormataVT(Valor_Total)  from @Valores;

6 – Exemplo – Gerando – XML com Version e Enconding:

DECLARE @xmlsample varchar(max),
@ArquivoXML Varchar(Max)

SET @xmlsample='<?xml version=“1.0” encoding=“UTF-8”?>’

Set @ArquivoXML= (Select CODIGO, Descricao from Produtos for XML Auto)

Select @xmlsample + Char(10) + @ArquivoXML

7 – Exemplo – Utilizando – UnPivot:

— Exemplo 1 —
CREATE TABLE dbo.CustomerPhones
(
CustomerID INT PRIMARY KEY, — FK
Phone1 VARCHAR(32),
Phone2 VARCHAR(32),
Phone3 VARCHAR(32)
);

INSERT dbo.CustomerPhones (CustomerID, Phone1, Phone2, Phone3)
VALUES (1,’705-491-1111′, ‘705-491-1110′, NULL),
(2,’613-492-2222′, NULL, NULL),
(3,’416-493-3333’, ‘416-493-3330’, ‘416-493-3339’);

— Forçando UnPivot sem utilizar o Operador UnPivot —
SELECT CustomerID, Phone = Phone1
FROM dbo.CustomerPhones WHERE Phone1 IS NOT NULL
UNION ALL
SELECT CustomerID, Phone = Phone2
FROM dbo.CustomerPhones WHERE Phone2 IS NOT NULL
UNION ALL
SELECT CustomerID, Phone = Phone3
FROM dbo.CustomerPhones WHERE Phone3 IS NOT NULL;

— Utilizando o Operador UnPivot —
SELECT CustomerID, Phone
FROM
(
SELECT CustomerID, Phone1, Phone2, Phone3
FROM dbo.CustomerPhones
) AS cp
UNPIVOT
(
Phone FOR Phones IN (Phone1, Phone2, Phone3)
) AS up;
— Exemplo 2 —
CREATE TABLE dbo.CustomerPhones2
(
CustomerID INT PRIMARY KEY, — FK
Phone1 VARCHAR(32),
PhoneType1 CHAR(4),
Phone2 VARCHAR(32),
PhoneType2 CHAR(4),
Phone3 VARCHAR(32),
PhoneType3 CHAR(4)
);

INSERT dbo.CustomerPhones2 VALUES
(1,’705-491-1111′, ‘cell’, ‘705-491-1110’, ‘home’, NULL,NULL),
(2,’613-492-2222′, ‘home’, NULL, NULL, NULL, NULL),
(3,’416-493-3333′, ‘work’, ‘416-493-3330’, ‘cell’,
‘416-493-3339’, ‘home’);

— Utilizando o Operador UnPivot —
SELECT CustomerID, Phone, PhoneType
FROM
(
SELECT CustomerID, Phone, PhoneType,
idp = SUBSTRING(Phones, LEN(Phones) – PATINDEX(‘%[^0-9]%’, REVERSE(Phones)) + 2, 32),
idpt = SUBSTRING(PhoneTypes, LEN(PhoneTypes) – PATINDEX(‘%[^0-9]%’, REVERSE(PhoneTypes)) + 2, 32)
FROM
(
SELECT CustomerID, Phone1, Phone2, Phone3,
PhoneType1, PhoneType2, PhoneType3
FROM dbo.CustomerPhones2
) AS cp
UNPIVOT
(
Phone FOR Phones IN (Phone1, Phone2, Phone3)
) AS p
UNPIVOT
(
PhoneType FOR PhoneTypes IN (PhoneType1, PhoneType2, PhoneType3)
) AS pt
) AS x
WHERE idp = idpt;

Pois bem pessoal, estes são os Short Scripts disponibilizados nesta relação, fique a vontade para compartilhar com seus contatos, amigos, DBAs, Profissionais de Banco de Dados, Amantes do SQL Server, Estudantes, enfim para todos.

Agradeço a sua visita, espero que este conteúdo possa te ajudar.

Um grande abraço e Viva a Argentina Futura Tri-Campeã Mundial de Futebol.

 

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação pela Uninove - Campus São Roque. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP - Faculdade de Informática e Administração Paulista de São Paulo. Pós-Graduado em Gestão da Tecnologia da Informação Faculdade - ESAMC Sorocaba. Cursando Mestrado em Ciências da Computação - UFSCar - Campus - Sorocaba. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1995 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator - Administrador de Banco de Dados - SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, Triggers. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping, para versões: 2000, 2005, 2008, 2008 R2, 2012 e 2014. Atualmente trabalho como Administrador de Banco de Dados no FIT - Instituto de Tecnologia da Flextronics, como também, Consultor em Projetos de Tunnig e Performance para clientes. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Possuo titulação Oficial Microsoft MVP - SQL Server renovada desde 2007.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s