Category Archives: T-SQL

T-SQL – Dynamic SQL

You can execute a string as SQL command.

DECLARE @myQuery AS NVARCHAR(MAX) = N'SELECT * FROM HR.Employees WHERE firstname = N''Sara''';
EXECUTE(@myQuery); -- 1st example
EXEC(@myQuery); -- 2nd example
EXEC sp_executesql @myQuery; -- 3rd example

You can use more secure way – pass parameter to avoid SQL injection.

DECLARE @name AS NVARCHAR(20) = N'Sara';
DECLARE @myQuery AS NVARCHAR(MAX) = N'SELECT * FROM HR.Employees WHERE firstname = @name';
EXEC sp_executesql @statement= @myQuery, -- set statement
		   @params=N'@name NVARCHAR(20)', -- declare parameter
		   @name= @name; -- set parameter

T-SQL – Temporary tables and table variables

Temporary tables can be used as helper tables. They are stored in tempdb database.
Global temporary tables start with ## and are visible in all sessions.
Local temporary tables start with # and are visible only in current session.

Table variables are similar to temporary tables, but they are not physically stored and should be used only for small amount of data and easy queries. They are visible in current batch only.
Temporary tables play normally with transactions and table variables not. Table variables are not rollbacked when transaction is rollbacked. It can be usefull to reproducing errors.

Creating examples:

CREATE TABLE #MyLocalTempTable -- table visible only in current session
(
	id INT NOT NULL PRIMARY KEY, 
	name NVARCHAR(100) NOT NULL
);

INSERT INTO #MyLocalTempTable VALUES(1, 'Bob');

CREATE TABLE ##MyGlobalTempTable -- table visible in all sessions
(
	id INT NOT NULL PRIMARY KEY, 
	name NVARCHAR(100) NOT NULL
);
INSERT INTO ##MyGlobalTempTable VALUES(1, 'Bob');

DECLARE @MyTableVariable AS TABLE -- variable visible only in current batch
(
	id INT NOT NULL PRIMARY KEY, 
	name NVARCHAR(100) NOT NULL
);
INSERT INTO @MyTableVariable VALUES(1, 'Bob');

Listing temporary objects:

SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';

T-SQL – Cursors

Cursors allow to process data row by row. Such processing is relatively slow.

Simple cursor usage.

DECLARE myCursor CURSOR FAST_FORWARD FOR  -- declare cursor
SELECT lastname, title FROM HR.Employees;

DECLARE @name AS NVARCHAR(255); -- declare variables which will be set by cursor
DECLARE @title AS NVARCHAR(25);

OPEN myCursor
FETCH NEXT FROM myCursor INTO @name, @title; -- get first record from cursor
WHILE  @@FETCH_STATUS = 0 --check if there is any record
	BEGIN
		PRINT 'Processing: ' + @name + ' (' + @title + ')'; -- process row data
		FETCH NEXT FROM myCursor INTO @name, @title; -- read next row
	END
CLOSE myCursor; -- finish work
DEALLOCATE myCursor;

There are different types of cursor:
a) INTENSIVE – makes a temporary copy of real table and operates on this copy.
b) SCROLL – allows you to move to the first/ last/ nth row.
c) READ_ONLY – updating is not allowed.
d) FORWARD_ONLY – allows to get always only the next row (not i.e. the last one).
e) FAST_FORWARD – means FORWARD_ONLY and READ_ONLY.
f) FOR UPDATE – allows to update specific columns.

T-SQL – Triggers

Triggers – are piece of code executed when insert/ update/ delete command is executed.

You can create trigger instead or after executed command.
You can run trigger when row is inserted, updated or deleted.
You can query edited data using inserted and deleted tables.

After trigger example.

CREATE TABLE Production.ProductUpdates -- create table to store information about edited data
(
	added INT,
	removed INT,
	pointInTime DATETIME2
);
GO

CREATE TRIGGER Production.tr_product_update -- create after trigger
ON Production.Products 
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
	IF @@ROWCOUNT <=0 RETURN; -- for performance reasons
	SET NOCOUNT ON; -- for performance reasons
	DECLARE @insertedCount AS INT = (SELECT COUNT(*) FROM inserted);
	DECLARE @removedCount AS INT = (SELECT COUNT(*) FROM deleted);
	INSERT INTO Production.ProductUpdates VALUES(@insertedCount, @removedCount, CURRENT_TIMESTAMP);
END;

INSERT INTO Production.Products VALUES('Product 1', 1, 1, 10, 0); -- insert product to run the trigger
SELECT * FROM Production.ProductUpdates; -- one row is added for inserted product (added = 1)

UPDATE Production.Products SET productname = 'Product 1a' WHERE productname = 'Product 1'; 
SELECT * FROM Production.ProductUpdates; -- one row is added for updated product (added = 1, removed = 1)

DELETE FROM Production.Products  WHERE productname = 'Product 1a';
SELECT * FROM Production.ProductUpdates; -- one row is added for deleted product (removed = 1)

Instead of trigger example.

DROP TRIGGER Production.tr_product_update; -- delete previous trigger
CREATE TRIGGER Production.tr_product_update -- create instead of trigger
ON Production.Products 
INSTEAD OF INSERT, UPDATE
AS
BEGIN
	IF @@ROWCOUNT <=0 RETURN; -- for performance reasons
	SET NOCOUNT ON; -- for performance reasons
	
	PRINT 'You cannot insert or update products now!';
END;

INSERT INTO Production.Products VALUES('Product 2', 1, 1, 10, 0); -- try to insert product to run the trigger
SELECT COUNT(*) FROM Production.Products WHERE productname = 'Product 2'; -- returns 0, row is not inserted

Trigger can rollback transaction which started it. See the example below.

DROP TRIGGER Production.tr_product_update; -- delete previous trigger
CREATE TRIGGER Production.tr_product_update
ON Production.Products 
INSTEAD OF INSERT, UPDATE
AS
BEGIN
	IF @@ROWCOUNT <=0 RETURN; -- for performance reasons
	SET NOCOUNT ON; -- for performance reasons
	
	PRINT 'You cannot insert or update products now!';
	ROLLBACK; -- rollback transaction which started the trigger
END;
GO

BEGIN TRAN -- start transaction which tries to insert product, then starts trigger and finally trigger rollbacks whole transaction
DECLARE @categoryId INT = (SELECT categoryid FROM Production.Categories WHERE categoryname = N'Drinks');
IF @categoryId IS NULL
BEGIN
	INSERT INTO Production.Categories VALUES(N'Drinks', N'Everything to drink');
	SET @categoryId = (SELECT IDENT_CURRENT('Production.Categories'));
END

INSERT INTO Production.Products VALUES('Water', 1, @categoryId, 4, 0);
COMMIT TRAN; -- transaction won't be commited as trigger rolls it back
GO

SELECT COUNT(*) FROM Production.Products WHERE productname = 'Water'; -- returns 0
SELECT COUNT(*) FROM Production.Categories WHERE categoryname = N'Drinks'; -- return 0

T-SQL – Procedures and functions

Procedures – are reusable pieces of code which perform an action and don’t return values.

Examples of creation and usage:

CREATE PROCEDURE Production.MakeBiggerPrice --creation
	@percent AS INT
AS
BEGIN
	UPDATE Production.Products SET unitprice = unitprice + unitprice * @percent / 100;
END;

EXECUTE Production.MakeBiggerPrice @percent = 5; -- usage A
EXECUTE Production.MakeBiggerPrice 5; -- usage B

Procedure with default and output parameter and turned off rows counter:

CREATE PROCEDURE Production.MakeBiggerPrice2 
	@percent AS INT,
	@categoryId AS INT = NULL, --default parameter
	@maxPrice AS MONEY OUTPUT --output parameter
AS
BEGIN
        IF @percent <= 0
                RETURN;
	SET NOCOUNT ON; --turns off counting edited rows (for performance optimisation)
	IF @categoryId IS NOT NULL
		UPDATE Production.Products SET unitprice = unitprice + unitprice * @percent / 100 
                WHERE @categoryId = @categoryId;
	ELSE
		UPDATE Production.Products SET unitprice = unitprice + unitprice * @percent / 100;
	
	SET @maxPrice = (SELECT MAX(unitprice) FROM Production.Products); --setting output parameter
	SET NOCOUNT OFF;
END;

DECLARE @max AS MONEY; --usage A
EXECUTE Production.MakeBiggerPrice2 @percent = 5, @maxPrice = @max OUTPUT;
PRINT 'Max price is: ' + CAST(@max AS VARCHAR(50));

DECLARE @max AS MONEY; --usage B
EXECUTE Production.MakeBiggerPrice2 @percent = 5, @categoryId = 1, @maxPrice = @max OUTPUT;
PRINT 'Max price is: ' + CAST(@max AS VARCHAR(50));

Dropping procedure:

DROP PROCEDURE Production.MakeBiggerPrice; -- simple way

IF OBJECT_ID('Production.MakeBiggerPrice2', 'P') IS NOT NULL -- with check
	DROP PROCEDURE Production.MakeBiggerPrice2;

Functions – are reusable pieces of code which perform an action to return values. They can return scalar values or tables. They cannot execute DDL commands and dynamic SQL, access temporary tables, use stored procedures.

Scalar user-defined function.

CREATE FUNCTION Production.GetMinPrice -- creation
(
	@categoryId AS INT
)
RETURNS MONEY -- returns scalar value
AS
BEGIN
	RETURN (SELECT MIN(unitprice) FROM Production.Products WHERE categoryid = @categoryId);
END

SELECT Production.GetMinPrice(1); --usage (SELECT, not EXEC)

Table-valued user-defined function.

CREATE FUNCTION Production.GetCheapProducts -- creation
(
	@categoryId AS INT
)
RETURNS @result TABLE -- returns defined table
(
  [id] INT,
  [price] MONEY
)
AS
BEGIN
	INSERT @result
	SELECT productId, unitprice FROM Production.Products 
        WHERE categoryid = @categoryId AND unitprice < (SELECT AVG(unitprice) FROM Production.Products);
	RETURN;
END

SELECT * FROM Production.GetCheapProducts(1); -- usage (select like from normal table)

Simplier table-valued function (no returned table declaration).

CREATE FUNCTION Production.GetCheapProducts2 -- creation
(
	@categoryId AS INT
)
RETURNS TABLE AS RETURN -- returs table
(
	SELECT productId, unitprice FROM Production.Products 
        WHERE categoryid = @categoryId AND unitprice < (SELECT AVG(unitprice) FROM Production.Products)
)

SELECT * FROM Production.GetCheapProducts2(1); -- usage (select like from normal table)

T-SQL – Dynamic SQL

You can execute a string as SQL command.

DECLARE @myQuery AS NVARCHAR(MAX) = N'SELECT * FROM HR.Employees WHERE firstname = N''Sara''';
EXECUTE(@myQuery); -- 1st example
EXEC(@myQuery); -- 2nd example
EXEC sp_executesql @myQuery; -- 3rd example

You can use more secure way – pass parameter to avoid SQL injection.

DECLARE @name AS NVARCHAR(20) = N'Sara';
DECLARE @myQuery AS NVARCHAR(MAX) = N'SELECT * FROM HR.Employees WHERE firstname = @name';
EXEC sp_executesql @statement= @myQuery, -- set statement
                   @params=N'@name NVARCHAR(20)', -- declare parameter
                   @name= @name; -- set parameter

T-SQL – Error handling

Errors have got a few properties:
a) number: standard SQL Server errors less than 50000, custom errors greater than 50000;
b) serverity: from 0 till 25, logged to log when > 16, closes connection and transactions when > 20;
c) message.

There are two ways to create an error: RAISERROR, THROW.

RAISERROR('Test', 16,0); -- raise error with message, sererity and state
THROW 50002, 'Test', 0; -- throw error with number, message, state and constant severity equal to 16

Only errors with severity > 10 are catched in TRY-CATCH clause. When serverity is lower error message is displayed, but code continues normal execution.

BEGIN TRY
	RAISERROR('Bug found!', 10, 0); -- severity = 10
	SELECT 'Line2'; --executed
END TRY
BEGIN CATCH
	SELECT 'Line3'; -- not executed
END CATCH

BEGIN TRY
	RAISERROR('Bug found!', 11,0); -- serverity = 11
	SELECT 'Line2'; -- not executed
END TRY
BEGIN CATCH
	SELECT 'Line3'; -- executed
END CATCH

You can read information about ocurred error.

BEGIN TRY
	RAISERROR('Bug found!', 12,0);
	SELECT 'Line2';
END TRY
BEGIN CATCH
	SELECT ERROR_NUMBER() AS errornumber
, ERROR_MESSAGE() AS errormessage
, ERROR_LINE() AS errorline
, ERROR_SEVERITY() AS errorseverity
, ERROR_STATE() AS errorstate;
END CATCH