Blocos de código úteis para SQL Server

Aqui neste exemplo eu listo alguns blocos de código importantes para o desenvolvimento utilizando linguagem Transact SQL. Os blocos são simples, eles tem em sua maioria a intenção de evitar que a sintaxe e outros detalhes sejam esquecidos. Eu acredito que tenha criado esse documento por estar com saudade do Microsoft SQL Server, pois hoje estou trabalhando em um projeto onde as informações são armazenas no Sharepoint.

-- Validar itens pertencentes a tabela de metadados sysObjects
IF EXISTS(SELECT TOP 1 * FROM sysObjects WHERE NAME = 'fk_tbAccess_tbClient')
    ALTER TABLE tbAccess DROP CONSTRAINT fk_tbAccess_tbClient
IF EXISTS(SELECT TOP 1 * FROM sysObjects WHERE NAME = 'tbClient' AND TYPE = 'U')
    DROP TABLE tbClient
IF EXISTS(SELECT TOP 1 * FROM sysObjects WHERE NAME = 'tbAccess' AND TYPE = 'U')
    DROP TABLE tbAccess

-- Criação de uma tabela pragramaticamente
CREATE TABLE tbClient
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    UNIQUEID UNIQUEIDENTIFIER DEFAULT(NEWID()) UNIQUE,
    FIRSTNAME VARCHAR(50) NOT NULL,
    LASTNAME VARCHAR(200),
    OCURRENCY DATETIME DEFAULT(GETDATE()) NOT NULL
)

CREATE TABLE tbAccess
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    CLIENTID INT NOT NULL,
    OCURRENCY DATETIME DEFAULT(GETDATE()) NOT NULL
)

-- Adicionar uma Foreign Key entre duas tabelas da base de dados
ALTER TABLE tbAccess
ADD CONSTRAINT fk_tbAccess_tbClient
FOREIGN KEY (CLIENTID)
REFERENCES tbClient(ID)
ON DELETE CASCADE

-- Adicionar nova coluna na tabela programaticamente
ALTER TABLE tbClient ADD NEWCOLUMN INT

-- Alterar tipo de dados de uma tabela programaticamente
ALTER TABLE tbClient ALTER COLUMN NEWCOLUMN DECIMAL(18,2)

-- Remover coluna de uma tabela programaticamente
ALTER TABLE tbClient DROP COLUMN NEWCOLUMN

-- Inserção
INSERT INTO tbClient (FIRSTNAME, LASTNAME) VALUES ('Amazonia', 'Região Norte')

INSERT INTO tbClient
    (UNIQUEID, FIRSTNAME, LASTNAME, OCURRENCY)
    VALUES
    (NEWID(), 'Brasil', 'América Latina', DATEADD(DAY, -1, GETDATE()))

-- Seleção
SELECT * FROM tbClient;   
SELECT * FROM tbClient ORDER BY ID DESC
SELECT * FROM tbClient WHERE ISNULL(ID, 0) = 1

-- Declaração de variáveis
DECLARE @id INT
DECLARE @fistName  VARCHAR(50),
        @ocurrency DATETIME

-- Valorização de variáveis
SET @id = (SELECT TOP 1 ID FROM tbClient)
SELECT TOP 1 @ocurrency = OCURRENCY FROM tbClient

-- Escrever valores em tela
PRINT @id
PRINT @ocurrency

-- Modelos de formatação de data
SELECT CONVERT(VARCHAR, OCURRENCY, 103) FROM tbClient /* Formato de data dd/MM/yyyy */
SELECT CONVERT(VARCHAR, OCURRENCY, 108) FROM tbClient /* Formato de hora hh/mm/ss   */
SELECT CONVERT(VARCHAR, OCURRENCY, 112) FROM tbClient /* Formato de data yyyyMMdd   */
SELECT CONVERT(VARCHAR, OCURRENCY, 110) FROM tbClient /* Formato de data MM-dd-yyyy */
SELECT CONVERT(VARCHAR, OCURRENCY, 102) FROM tbClient /* Formato de data yyyy.MM.dd */

-- Looping
DECLARE @iterator INT
SET @iterator = 0
WHILE (@iterator < 50) BEGIN
    INSERT INTO tbAccess (CLIENTID, OCURRENCY) VALUES (1, DATEADD(DAY, @iterator, GETDATE()))
    SET @iterator = @iterator + 1
END

-- Inner Join
SELECT
    C.FIRSTNAME,
    A.OCURRENCY
FROM
    tbClient C
    INNER JOIN tbAccess A ON C.ID = A.CLIENTID

-- Left Join
SELECT
    C.FIRSTNAME,
    A.OCURRENCY
FROM
    tbClient C
    LEFT JOIN tbAccess A ON C.ID = A.CLIENTID

-- Group By e Inner Join
SELECT
    C.FIRSTNAME,
    MAX(A.OCURRENCY) AS "Máxima Data"
FROM
    tbClient C
    INNER JOIN tbAccess A ON C.ID = A.CLIENTID
GROUP BY
    C.FIRSTNAME

-- Group By e Left Join
SELECT
    C.FIRSTNAME,
    MAX(A.OCURRENCY) AS MAXIMA_DATA
FROM
    tbClient C
    LEFT JOIN tbAccess A ON C.ID = A.CLIENTID
GROUP BY
    C.FIRSTNAME

-- Best Praticie
-- Criação de procedure de captura de exceptions
-- http://msdn.microsoft.com/pt-br/library/ms175976.aspx
IF OBJECT_ID('spGetErrorInfo', 'P') IS NOT NULL
    DROP PROCEDURE spGetErrorInfo;
GO

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

-- Bloco Try/Catch
BEGIN TRY   
    -- Simula uma exception
    SELECT 1/0;
END TRY
BEGIN CATCH
    -- Executa procedure de captura de exceptions   
    EXECUTE spGetErrorInfo;
END CATCH;

-- Função simples
IF EXISTS(SELECT * FROM sysObjects WHERE NAME = 'fnRetornaTriplo')
    DROP FUNCTION fnRetornaTriplo
GO

CREATE FUNCTION fnRetornaTriplo(@variavel INT)
    RETURNS INT
BEGIN

    RETURN @variavel * @variavel

END
GO

-- Função tabela
IF EXISTS(SELECT * FROM sysObjects WHERE NAME = 'fnRetornaTabela')
    DROP FUNCTION fnRetornaTabela
GO

CREATE FUNCTION fnRetornaTabela(@variavel INT)
    RETURNS @return TABLE(ID INT, CLIENTGUID UNIQUEIDENTIFIER)
BEGIN
   
    INSERT INTO @return
    SELECT ID, UNIQUEID
    FROM tbClient
    WHERE ID = @variavel

    RETURN

END
GO

-- Executa função
SELECT dbo.fnRetornaTriplo(10)
SELECT * FROM dbo.fnRetornaTabela(1)

-- Criar visão
IF EXISTS(SELECT * FROM sysObjects WHERE NAME = 'vwClient')
    DROP VIEW vwClient
GO

CREATE VIEW vwClient
AS
    SELECT
        ID,
        FIRSTNAME
    FROM
        tbClient
GO

-- Utilizando UNION
SELECT * FROM tbClient
UNION
SELECT * FROM tbClient
UNION
SELECT * FROM tbClient

-- Utilizando UNION ALL
SELECT * FROM tbClient
UNION ALL
SELECT * FROM tbClient
UNION ALL
SELECT * FROM tbClient

-- Consulta de metadados da base de dados
SELECT
    Objetos.name,
    Colunas.name,
    TipoDados.name,
    Colunas.length
FROM
    sysColumns Colunas
    INNER JOIN sysTypes TipoDados ON Colunas.xtype = TipoDados.xtype
    INNER JOIN sysObjects Objetos ON Colunas.id = Objetos.id
WHERE
    Objetos.Type = 'U'
ORDER BY
    Objetos.name,
    Colunas.colorder

Por

Fernando Henrique Inocêncio Borba Ferreira