Advertisements

Archive

Archive for February, 2015

SQL Basics – What are Row Constructors?

February 16, 2015 1 comment

Constructors, as the name suggests means to create an instance of an Object in any Object Oriented Programming language.
 

Here in SQL Server or T-SQL, ROW Constructor or Table Value Constructor means to create a row set by using the VALUES() clause. This allows multiple rows of data to be specified in a single DML statement. And this VALUES() clause can be used with the SELECT, INSERT and MERGE statements.
 

In the examples below we will see how they can be used and are helpful at times:
 

Usage #1. You can create a simple set of rows with a SELECT FROM statement:

SELECT * 
FROM (
VALUES
	(1, 'cust 1', '(111) 111-1111', 'address 1'),
	(2, 'cust 2', '(222) 222-2222', 'address 2'),
	(3, 'cust 3', '(333) 333-3333', 'address 3'),
	(4, 'cust 4', '(444) 444-4444', 'address 4'),
	(5, 'cust 5', '(555) 555-5555', 'address 5')
) AS C (CustID, CustName, phone, addr);

 

Usage #2. You can use it with INSERT statement while inserting rows in a table:

CREATE TABLE dbo.Customer (
	CustID		INT, 
	CustName VARCHAR(100), 
	phone		VARCHAR(20), 
	addr		VARCHAR(500)
)

INSERT INTO dbo.Customer (CustID, CustName, phone, addr)
VALUES
(1, 'cust 1', '(111) 111-1111', 'address 1'),
(2, 'cust 2', '(222) 222-2222', 'address 2'),
(3, 'cust 3', '(333) 333-3333', 'address 3'),
(4, 'cust 4', '(444) 444-4444', 'address 4'),
(5, 'cust 5', '(555) 555-5555', 'address 5');

 

Usage #3. You can create mixed row-sets from manually entered values and from other tables:

SELECT * 
FROM (
VALUES
	(1, 'cust 1', '(111) 111-1111', 'address 1'),
	(2, 'cust 2', '(222) 222-2222', 'address 2'),
	(3, 'cust 3', '(333) 333-3333', 'address 3'),
	(4, 'cust 4', '(444) 444-4444', 'address 4'),
	((SELECT CustID FROM dbo.Customer WHERE CustID IN (5)),
	 (SELECT CustName FROM dbo.Customer WHERE CustID IN (5)),
	 (SELECT phone FROM dbo.Customer WHERE CustID IN (5)),
	 (SELECT addr FROM dbo.Customer WHERE CustID IN (5))
	)
) AS C (CustID, CustName, phone, addr);

 

Usage #4. You can use them with JOINS, without need to create #Temp-Table or Table-Variable to store temporary data:

SELECT 
	C.CustName, 
	O.ProductName
FROM dbo.Customer C
LEFT JOIN (
VALUES 
	(101, 1, 'Apple'), 
	(102, 3, 'Orange'),
	(103, 5, 'Banana')
) AS O (OrderID ,CustID, ProductName)
ON O.CustID = C.CustID

Row Constructor 01
 

Usage #5. You can use them with MERGE statement, again without need to create #Temp-Table or Table-Variable to store temporary data:

MERGE INTO dbo.Customer as Target
USING (
VALUES
	(5, 'cust 5', '(555) 555-5555', 'address 5 updated'),
	(6, 'cust 6', '(666) 666-6666', 'address 6')
) AS Source (CustID, CustName, phone, addr)
ON Target.CustID = Source.CustID
WHEN MATCHED THEN UPDATE SET 
	Target.CustName = Source.CustName, 
	Target.phone = Source.phone, 
	Target.addr = Source.addr
WHEN NOT MATCHED BY Target THEN
INSERT (
	CustID, 
	CustName, 
	phone, 
	addr
) 
VALUES (
	Source.CustID, 
	Source.CustName, 
	Source.phone, 
	Source.addr
);

select * from  dbo.Customer;

Row Constructor 02
 

Thus, Row Constructors or Table Value Constructors are very handy when dealing with fixed set of row sets used for temporary purpose, without need of creating and storing them in #Temp-Tables or Table-Variables.
 

–> Final Cleanup

DROP TABLE dbo.Customer

Advertisements

Capture multiple errors in TRY CATCH by using THROW statement

February 4, 2015 4 comments

This post relates to my earlier post [link] where I mentioned on benefit of using THROW clause with same SQL examples.

The THROW clause was introduced in SQL Server 2012 and may be replacing the RAISERROR function in near future.

Normally the SQL statements returns single error, but some SQL statements returns more than one error message when they go wrong due to some reason or exception.
 

–> On executing the below BACKUP statement in SSMS we can see we get two errors:

BACKUP DATABASE [AdventureWorks2012] 
TO DISK='E:\FOLDER_NOT_EXISTS\test.bak'

The above code throws 2 errors with Error-Message IDs 3201 & 3013, as shown below:

error messages:
Msg 3201, Level 16, State 1, Line 2
Cannot open backup device ‘E:\FOLDER_NOT_EXISTS\test.bak’. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.

 

-> But when we want to track these errors by using RAISERROR function it just returns the last (single) error message and its details, and the previous error message details are not returned by this function.

BEGIN TRY
	BACKUP DATABASE [AdventureWorks2012] 
	TO DISK='E:\FOLDER_NOT_EXISTS\test.bak'
END TRY
BEGIN CATCH
	DECLARE @msg VARCHAR(1000) = ERROR_MESSAGE()
	RAISERROR(@msg,16,0)
END CATCH

Here, only 1 error message will be returned:

error messages:
Msg 50000, Level 16, State 0, Line 7
BACKUP DATABASE is terminating abnormally.

 

–> With the new THROW clause you won’t see any issue of omitting the previous errors, as it returns all error details thrown by the SQL Statement itself.

BEGIN TRY
	BACKUP DATABASE [AdventureWorks2012] 
	TO DISK='E:\FOLDER_NOT_EXISTS\test.bak'
END TRY
BEGIN CATCH
	THROW;
END CATCH

The above statement throws both the error details as we saw in the first example:
error messages:
Msg 3201, Level 16, State 1, Line 2
Cannot open backup device ‘E:\FOLDER_NOT_EXISTS\test.bak’. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.

 

Thus, if you are on SQL Server 2012 and above you must consider using THROW clause instead of the RAISERROR function.