In this article, we’ll take a look at a brief TSQL history and a few examples of loops, conditionals, and stored procedures
TSQL also named T-SQL or Transact-SQL is an SQL extension used in SQL Server created and used by Sybase to write queries. In this article, we will talk about the Transact-SQL history including some SQL history to understand the evolution.
To understand T-SQL, you need to study SQL first. The SQL which was initially SEQUEL (Structured English Query Language). This language was initially developed by IBM in San Jose, California. The name SEQUEL was changed to SQL later due to some trademark problems with a UK company, but now SEQUEL is the official pronunciation of SQL
SQL now stands for Structured Query Language and it is a standard used by the databases to query Structured Languages. Even several NoSQL databases use similar languages.
There were several revisions of the SQL. The first revision was on 1986 (SQL-86) the second one on 1989 (SQL-89) and the main revision was SQL 92 (SQL-92). After that major revision, there were other minor revisions:
- SQL 99 (included some OLAP concepts, Boolean data types, role-based access)
- SQL 2003 (included some XML features, windows functions, more OLAP features, and other features)
- SQL 2006 (includes more features related to Storing XML, XQuery, etc.)
- SQL 2008 (includes definitions for TRUNCATE statements, FETCH Clauses, INSTEAD OF)
- SQL 2011 (includes revisions for temporal data, some additional definitions for Windows Functions and also the FETCH clause
- SQL 2016 includes features related to JSON data, polymorphic table functions, and row pattern matching
- SQL 2019 is related to the multidimensional arrays
T-SQL is based on SQL but extended. T-SQL means Transaction SQL and it was originally used by Sybase (a database than was popular in the past) and then after a while used by Microsoft SQL Server. If you did not know, SQL Server was based in Sybase technology at the beginning. For more information about SQL Server history, refer to this article:
In this article, we will not teach SQL which is a database query language used by the databases. We will focus on Transact-SQL that includes some functionality not necessarily included in SQL.
You can use Transact-SQL to solve some programming problems not related to databases. What I am trying to say is that you can write code and solve problems not related to databases.
For queries, we will use the Adventureworks database.
Let’s start with some code:
The first example is the famous Hello world. To do this we can use the following code:
SELECT 'hello world'
You could also use the PRINT command:
PRINT 'hello world'
The select prints as a column the result of Hello world, whereas the PRINT, prints as a message.
You can use variables. The following example will show how to select characters, numbers, and dates:
declare @integer integer=0
declare @char varchar(20)='This is a value'
declare @myDate date='8/27/2017'
select @integer as integer, @char as character, @myDate as Date
For more information about data types, refer to this article:
The next example shows how to use a variable in a query. We will use the variable in a query. We will store the value 3 in the variable departmentid and show the department id and name from the table humanresource.department where department id is equal to 3:
DECLARE @departmentid integer=3
In this programming language, you do not have a loop sentence like in C++, C#, and Java. You use the WHILE Sentence instead.
The following example will create a table named myIDs and insert 10 values using a WHILE sentence:
CREATE TABLE myIDs( id integer)
DECLARE @counter int=0
INSERT INTO myIDs(id) values (@counter)
SELECT COUNT(id) FROM myIDs
TSQL if conditionals
The use of conditionals uses the IF sentences. The use of IF and ELSE is similar to other programming languages, the following examples show the use of IF and ELSE.
The following example detects how old is a person with the date. The function GETDATE gets the current date and the variable myBirthDay store the birthday date. The function DATEDIFF gets the number of years between the current date and the birthday. Finally, the if sentence classifies as adult or children according to the age calculated. If the age is higher than 18, the person is an adult. Otherwise, the person is a child.
DECLARE @myBirthDay date= '03/19/1979'
DECLARE @currentDate datetime=GETDATE()
DECLARE @age integer=DATEDIFF(year,@myBirthDay,@currentDate)
SELECT 'Adult' as age
SELECT 'Child' as age
TSQL Stored Procedure
Using Stored Procedures is a best practice and it is highly recommended in SQL Server. If possible, always use stored procedures instead of a raw T-SQL query for security and performance.
The following example will show how to use a stored procedure with a parameter. In this stored procedure, we have a stored procedure named CountryName and we pass as a parameter the Country Region Code. We do a select of two columns of the table Person.CountryRegion from the Adventureworks database where the region code is specified as a parameter:
CREATE PROCEDURE CountryName
If we want to invoke the stored procedure, the following code should be used:
We are calling the stored procedure CountryName and we are sending the Region Code.
In this article, we learned about SQL and Transact-SQL. We first learn the history first and then we jump into basic code. We learned loops, conditionals, and stored procedures.
He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte