brovish

-- Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
-- Date: 2020-05-31
-- Description: Collect T-SQL Error Events using an Extended Events Buffer
--		The script automatically detects whether you're in an Azure SQL DB, or a regular SQL Server instance.

SET NOCOUNT ON;

DECLARE
  @SourceLinkedServer SYSNAME
, @MinimumSeverity INT

brovish

CREATE TABLE RockClub (
	ID INT IDENTITY(1, 1), 
	Address_NS NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AI,
	Address_VS VARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AI,
	Address_NW NVARCHAR(128) COLLATE Latin1_General_CI_AI,
	Address_VW VARCHAR(128) COLLATE Latin1_General_CI_AI
)

INSERT INTO RockClub (Address_NS, Address_VS, Address_NW, Address_VW) VALUES (N'Saarbrücker Straße 24, 10405 Berlin','Saarbrücker Straße 24, 10405 Berlin',N'Saarbrücker Straße 24, 10405 Berlin','Saarbrücker Straße 24, 10405 Berlin')

brovish

create proc dbo.MyProc
as
begin
    set xact_abort on
    begin try
        begin tran
            /* Some logic here  */
        commit
    end try
    begin catch

brovish

SELECT
  'create table [' + so.name + '] (' + o.list + ')' +
  CASE
    WHEN tc.Constraint_Name IS NULL THEN ''
    ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name + ' PRIMARY KEY ' + ' (' + LEFT(j.List, LEN(j.List) - 1) + ')'
  END
FROM sysobjects so
CROSS APPLY (SELECT
    '  [' + column_name + '] ' +
    data_type +

brovish

--IF you want your COLUMN TO be NOT NULL, THEN you will have TO CONVERT an appropriate DEFAULT
--   VALUE other than NULL.
SELECT ItemID,
       ItemType,
       CAST(NULL AS DATETIME) AS DateSold
INTO   NewInventory
FROM   Inventory
WHERE  ItemID >= 11000

brovish

PRINT 'This script is not meant to execute all at once!';
PRINT 'Please highlight and execute each section individually.';
GO
SET PARSEONLY ON;
GO
 
PRINT 'Doin'' sumthin''';
 
GO
-- Place at end of script to make sure PARSEONLY is not left ON.

brovish

DECLARE @RequirementsMet BIT;
 
 
PRINT 'Check and prepare things...';
SET @RequirementsMet = 0;
 
 
IF (@RequirementsMet = 0)
BEGIN;
    RAISERROR('error message...', 10, 1) WITH NOWAIT;

brovish

-- Listing 10-11: Using IF COLUMNS_UPDATED() for Testing Updates on Columns 7 and 9
CREATE TRIGGER truProducts ON PRODUCTS FOR UPDATE
AS
IF @@ROWCOUNT = 0
  RETURN

IF SUBSTRING (COLUMNS_UPDATED (), 1, 1) & 64 = 64
  OR SUBSTRING (COLUMNS_UPDATED(), 2, 1) = 1
BEGIN
  IF EXISTS (SELECT * FROM inserted WHERE UnitsInStock < ReorderLevel)

brovish

-- Listing 10-28: Using an INSTEAD OF Trigger to Handle a Data Feed
CREATE TABLE FeedTarget
(
  ID    int      NOT NULL PRIMARY KEY,
  Descr char (5) NOT NULL
)
GO

CREATE TRIGGER tri_FeedTarget ON FeedTarget INSTEAD OF INSERT
AS

brovish

-- Listing 7.16: TRY…CATCH handling of deadlock exceptions, in T-SQL.
DECLARE @retries INT;
SET @retries = 4;
WHILE (@retries > 0)
BEGIN
    BEGIN TRY
    	BEGIN TRANSACTION ;
    	-- place sql code here
    	SET @retries = 0 ;
    	COMMIT TRANSACTION ;