Referência rápida SQL Server


Create Unique Index

DROP INDEX UNIQUE_Supplier_Name ON Supplier
CREATE UNIQUE INDEX UNIQUE_Supplier_Name ON Supplier (ReferenceName)

Cursor

DECLARE @brandID int

DECLARE cursor1 CURSOR FOR
SELECT brandID from Brand (NOLOCK)

OPEN cursor1

FETCH NEXT FROM cursor1 INTO
	@brandID

WHILE @@FETCH_STATUS = 0
BEGIN

	print '> loop cursor1'

	print 'brandID=' + convert(varchar(10), @brandID)

	FETCH NEXT FROM cursor1 INTO
		@brandID
END

CLOSE cursor1
DEALLOCATE cursor1

Paginação

SELECT
	m.id,
	COUNT(1) OVER()
FROM
	Movie m
WHERE
	m.ChannelID = 1
ORDER BY
	m.Created
OFFSET @OFFSET ROWS FETCH NEXT @COUNT ROWS ONLY
CREATE PROCEDURE dbo.BrandPagedList (
	@page int,
	@pageSize int
) AS
BEGIN

WITH ListResult AS
(
	SELECT
		br.BrandID
		, br.[Name]
    
		, ROW_NUMBER() OVER(ORDER BY br.[Name] ASC) AS rowNumber
	FROM
		Brand (NOLOCK) br
)
SELECT *, (SELECT MAX(rowNumber) FROM ListResult) as TotalRows FROM ListResult WHERE rowNumber BETWEEN ((@pageSize * (@page - 1)) + 1) AND (@pageSize * @page);

Transaction + RaiseError

BEGIN TRY
	BEGIN TRANSACTION
	PRINT '--BEGIN TRANSACTION'

	--Execute...

	COMMIT
	PRINT '--COMMIT'

END TRY
BEGIN CATCH

	DECLARE @errorMessage nvarchar(max), @errorSeverity int, @errorState int;

	SELECT
		@errorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)),
		@errorSeverity = ERROR_SEVERITY(),
		@errorState = ERROR_STATE();

	PRINT '--ROLLBACK'
	ROLLBACK

	RAISERROR (@errorMessage, 16, -1);
	RETURN

END CATCH

Retorna registros em texto separado por vírgula

SELECT
	a.ItemID,
	(SELECT
		STUFF(
				(SELECT ',' + CONVERT(VARCHAR(10), si.SupplierID) FROM SupplierItem si (NOLOCK) WHERE a.ItemID = si.ItemID AND si.Active = 1 FOR XML PATH(''))
			,1,1,'')
	) as SupplierIdList
FROM
	Item a (nolock)
WHERE
	a.ItemID in (303216, 5)

Obtém itens de uma tabela de fila usando UPDLOCK

CREATE PROCEDURE dbo.QueueGetNext (
	@queueOperationId INT = null) as
BEGIN

	DECLARE @queueId VARCHAR(36) = NULL

	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	BEGIN TRANSACTION

		SELECT TOP 1
			@queueId = QueueId
		FROM
			[Queue] WITH (UPDLOCK)
		WHERE
			QueueStatusId = 1 --Incluido
			AND (@queueOperationId IS NULL OR QueueOperationId = @queueOperationId)
		ORDER BY
			RequestDate ASC

		--Atualiza
		UPDATE
			[Queue]
		SET
			QueueStatusId = 2 --Processando
		WHERE
			QueueId = @queueId

	COMMIT TRANSACTION

	SELECT * FROM [Queue] WHERE QueueId = @queueId

END

Cross join

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join

SELECT
	p.BusinessEntityID, t.Name AS Territory
FROM
	Sales.SalesPerson p
CROSS JOIN
	Sales.SalesTerritory t
ORDER BY
	p.BusinessEntityID;

Data do primeiro dia do mês

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'fnFirstMonthDay' AND type in ('FN', 'IF', 'TF', 'FS', 'FT'))
	DROP FUNCTION dbo.fnFirstMonthDay
go

CREATE FUNCTION dbo.fnFirstMonthDay(@date DATE) RETURNS DATE AS
BEGIN

	IF @date IS NULL
		RETURN NULL

	RETURN DATEADD(day, ((DATEPART(D, @date) -1) * -1), @date)

END

Data do primeiro dia da semana

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'fnFirstWeekDay' AND type in ('FN', 'IF', 'TF', 'FS', 'FT'))
	DROP FUNCTION dbo.fnFirstWeekDay
go

CREATE FUNCTION dbo.fnFirstWeekDay(@date DATE) RETURNS DATE AS
BEGIN

	IF @date IS NULL
		RETURN NULL

	RETURN DATEADD(day, ((DATEPART(DW, @date) -1) * -1), @date)

END

Obtém somente os caracteres numéricos de um texto

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'fnGetNumericCharacters' AND type in ('FN', 'IF', 'TF', 'FS', 'FT'))
	DROP FUNCTION fnGetNumericCharacters
GO

CREATE FUNCTION dbo.fnGetNumericCharacters(@text VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS
BEGIN

	IF @text IS NULL
		RETURN NULL

	DECLARE @index INT = PATINDEX('%[^0-9]%', @text)

	WHILE @index > 0
	BEGIN
		SET @text = STUFF(@text, @index, 1, '' )
		SET @index = PATINDEX('%[^0-9]%', @text )
	END

	IF len(@text) = 0
		RETURN NULL

	RETURN @text

END
GO

Remove acentuação de um texto

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'fnRemoveDiacritics' AND type in ('FN', 'IF', 'TF', 'FS', 'FT'))
	DROP FUNCTION dbo.fnRemoveDiacritics
GO

CREATE FUNCTION dbo.fnRemoveDiacritics (@text VARCHAR(MAX))
RETURNS VARCHAR(MAX) AS
BEGIN

	SELECT @text = (@text collate SQL_Latin1_General_Cp1251_CS_AS)

    RETURN @text

END

Remove caracteres especiais de um texto

IF EXISTS(SELECT * FROM sys.objects where name = 'fnRemoveSpecialCharacters' AND type in ('FN', 'IF', 'TF', 'FS', 'FT'))
	DROP FUNCTION dbo.fnRemoveSpecialCharacters
GO

CREATE FUNCTION dbo.fnRemoveSpecialCharacters (@text VARCHAR(MAX), @allowSpaces BIT = 1)
RETURNS VARCHAR(MAX) AS
BEGIN

    DECLARE @matchExpression AS VARCHAR(100)

	IF @allowSpaces = 1
		SET @matchExpression = '%[^a-z0-9 ]%'
	ELSE
		SET @matchExpression = '%[^a-z0-9]%'

	WHILE PatIndex(@matchExpression, @text) > 0
        SET @text = Stuff(@text, PatIndex(@matchExpression, @text), 1, '')

    RETURN @text

END
GO

Converte um texto para ser utilizado como url

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'fnGetUrl' AND type in ('FN', 'IF', 'TF', 'FS', 'FT'))
	DROP FUNCTION dbo.fnGetUrl
go

CREATE FUNCTION dbo.fnGetUrl(@text varchar(200)) RETURNS VARCHAR(200) AS
BEGIN

	--remove
	SET @text = replace(@text, '&', '')
	SET @text = replace(@text, '.', '')
	SET @text = replace(@text, '(', '')
	SET @text = replace(@text, ')', '')
	SET @text = replace(@text, '''', '')
	SET @text = replace(@text, '"', '')
	SET @text = replace(@text, '%', '')
	SET @text = replace(@text, '$', '')
	SET @text = replace(@text, '+', '')

	--separador
	SET @text = replace(@text, ' e ', '-')
	SET @text = replace(@text, ',', '-')
	SET @text = replace(@text, ' - ', '-')
	SET @text = replace(@text, '/', '-')
	SET @text = replace(@text, '\', '-')
	SET @text = replace(@text, ' ', '-')

	SET @text = replace(@text, '--', '-')

	--remove acentuação
	SELECT @text = @text Collate SQL_Latin1_General_CP1253_CI_AI

	SET @text = ltrim(rtrim(lower(@text)))

	RETURN @text

END
GO

Transforma texto separado por vírgula em registros

IF EXISTS(SELECT name FROM sys.objects WHERE name = 'fnSplit' AND type in ('FN', 'IF', 'TF', 'FS', 'FT'))
	DROP FUNCTION dbo.fnSplit
GO

CREATE  FUNCTION dbo.fnSplit(@text VARCHAR(MAX), @separator VARCHAR(20) = ' ')
RETURNS @strings TABLE
(   
  position int IDENTITY PRIMARY KEY,
  value VARCHAR(MAX)
)
AS
BEGIN

DECLARE @index int = -1

WHILE (LEN(@text) > 0)
  BEGIN
    SET @index = CHARINDEX(@separator , @text)
    IF (@index = 0) AND (LEN(@text) > 0)
      BEGIN  
        INSERT INTO @strings VALUES (@text)
          BREAK
      END
    IF (@index > 1)
      BEGIN  
        INSERT INTO @strings VALUES (LEFT(@text, @index - 1))  
        SET @text = RIGHT(@text, (LEN(@text) - @index))
      END
    ELSE
      SET @text = RIGHT(@text, (LEN(@text) - @index))
    END
  RETURN
END

Completa texto com espaços ou zeros

IF EXISTS(SELECT name FROM sys.objects WHERE Name = 'fnPadString' AND type in ('FN', 'IF', 'TF', 'FS', 'FT'))
	DROP FUNCTION dbo.fnPadString
GO

CREATE FUNCTION dbo.fnPadString (@text VARCHAR(MAX), @padWith char(1), @length int) RETURNS VARCHAR(MAX) AS
BEGIN

DECLARE @ret VARCHAR(MAX)
SELECT @ret = ISNULL(REPLICATE(@padWith, @length - LEN(ISNULL(@text,0))), '') + @text
RETURN @ret

END
GO

Select usando WITH

--drop table [Local]

CREATE TABLE [Local]
	(LocalID int not null,
	LocalPaiID int null,
	Nome varchar(50) not null)
go

INSERT INTO [Local] values (100, null, 'Bloco 1');
	INSERT INTO [Local] values (110, 100, 'Prédio 1');
			INSERT INTO [Local] values (111, 110, '1º Andar');
			INSERT INTO [Local] values (112, 110, '2º Andar');
			INSERT INTO [Local] values (113, 110, '3º Andar');
			INSERT INTO [Local] values (114, 110, '4º Andar');
	INSERT INTO [Local] values (120, 100, 'Prédio 2');
			INSERT INTO [Local] values (121, 120, '1º Andar');
			INSERT INTO [Local] values (122, 120, '2º Andar');
			INSERT INTO [Local] values (123, 120, '3º Andar');
	INSERT INTO [Local] values (12, 100, 'Prédio 3');
INSERT INTO [Local] values (200, null, 'Bloco 2');
	INSERT INTO [Local] values (210, 200, 'Prédio 1');
	INSERT INTO [Local] values (220, 200, 'Prédio 2');
	INSERT INTO [Local] values (230, 200, 'Prédio 3');
INSERT INTO [Local] values (300, null, 'Bloco 3');
go


WITH Loc AS
		(SELECT 1 as Nivel, l.LocalID, l.Nome, CONVERT(VARCHAR(1000), l.Nome) as CaminhoCompleto FROM [Local] l (NOLOCK) WHERE l.LocalPaiID IS NULL
		UNION ALL
		SELECT Nivel+1, l.LocalID, l.Nome, CONVERT(VARCHAR(1000), Loc.CaminhoCompleto + ' / ' + l.Nome) as CaminhoCompleto
		FROM [Local] l (NOLOCK)
		INNER JOIN Loc on l.LocalPaiID = Loc.LocalID
		WHERE l.LocalPaiID is not null
		)
	SELECT LocalID, Nome, CaminhoCompleto FROM Loc;
go
IF EXISTS(SELECT name FROM sys.objects WHERE Name = 'fn_Local_Caminho_Completo' AND type in ('FN', 'IF', 'TF', 'FS', 'FT'))
	DROP FUNCTION dbo.fn_Local_Caminho_Completo
GO

CREATE FUNCTION dbo.fn_Local_Caminho_Completo(@LocalID int)
returns VARCHAR(1000)
AS
BEGIN

	DECLARE @caminho varchar(1000);

	WITH Loc AS
		(SELECT l.LocalID, l.LocalPaiID, l.Nome, CONVERT(VARCHAR(1000), l.Nome) as CaminhoCompleto FROM [Local] l (NOLOCK) WHERE l.LocalID = @LocalID
		UNION ALL
		SELECT l.LocalID, l.LocalPaiID, l.Nome, CONVERT(VARCHAR(1000), l.Nome + ' / ' + Loc.CaminhoCompleto) as CaminhoCompleto
		FROM [Local] l (NOLOCK)
		INNER JOIN Loc on l.LocalID = Loc.LocalPaiID
	)
	SELECT @caminho = CaminhoCompleto FROM Loc WHERE LocalPaiID IS NULL

	return @caminho;
END
go

ALTER TABLE [Local] ADD CaminhoCompleto AS dbo.fn_Local_Caminho_Completo(LocalID);
go

select * from [Local];
go

Comentários