In this article, we’ll take a look at a brief TSQL history and a few examples of loops, conditionals, and stored procedures
Introduction
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.
Requirements
- SQL Server installed and SSMS to run queries and code
- The Adventureworks database installed
SQL history
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
TSQL history
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:
TSQL basis
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.
Requirements
In order to start, you will need SQL Server installed and SQL Server Management Studio (SSMS). You could use other tools to write the code, but this is the official tool.
For queries, we will use the Adventureworks database.
TSQL samples
Let’s start with some code:
The first example is the famous Hello world. To do this we can use the following code:
|
1 |
SELECT 'hello world' |
You could also use the PRINT command:
|
1 |
PRINT 'hello world' |
The select prints as a column the result of Hello world, whereas the PRINT, prints as a message.
TSQL variables
You can use variables. The following example will show how to select characters, numbers, and dates:
|
1 2 3 4 5 |
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:
|
1 2 3 4 |
DECLARE @departmentid integer=3 SELECT [DepartmentID],[Name] FROM [HumanResources].[Department] WHERE [DepartmentID]=@departmentid |
TSQL LOOPS
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE myIDs( id integer) DECLARE @counter int=0 WHILE @counter<10 BEGIN INSERT INTO myIDs(id) values (@counter) SELECT @counter=@counter+1 END 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.
|
1 2 3 4 5 6 7 |
DECLARE @myBirthDay date= '03/19/1979' DECLARE @currentDate datetime=GETDATE() DECLARE @age integer=DATEDIFF(year,@myBirthDay,@currentDate) IF @age>18 SELECT 'Adult' as age ELSE 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:
|
1 2 3 4 5 6 7 8 9 |
CREATE PROCEDURE CountryName @CountryRegionCode nvarchar(2) AS SELECT [CountryRegionCode], [Name] FROM [Person].[CountryRegion] WHERE CountryRegionCode=@CountryRegionCode |
If we want to invoke the stored procedure, the following code should be used:
|
1 2 |
execute CountryName @CountryRegionCode='BO' |
We are calling the stored procedure CountryName and we are sending the Region Code.
Conclusion
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
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023