Transact-SQL

By Guest Authors on 20 December 2000 | Tags: Tutorials , Transact-SQL

This article was written by Rob Taylor (taylo in the forums). It talks about Transact-SQL and stored procedures and how they would be useful for ASP developers.

What is Transact SQL?

Transact SQL, also called T-SQL, is Microsoft's extension to the ANSI SQL language. It is the driving force of Microsoft's SQL Server and is a dynamic database programming language. There have been several extensions added to the ANSI SQL language that have become their own SQL language. Oracles PL/SQL is another. So if you were using an Oracle database, you would do database programming in PL/SQL. Just like you use T-SQL with SQL server.

How is T-SQL Used?

T-SQL is written inside of a stored procedure. A stored procedure is a stored set of SQL commands that sit on the physical server. In this case the SQL server. They are compiled after their first use and take heavy burden off the server. Often with ASP development you run in to situations where interaction between the database and the application are rapidly in succession. Like this:

A new user comes in. Lets put him in the users table. Return the identity. Now lets update the member count for his company in the Company table. Add him to the company member’s table with his new ID. Another company member sponsored him so lets track all that as well.

In a normal ASP application we would be doing ALL of the above from the application. We would execute 1 SQL statement, come back and do the next, come back and do the next, etc.... Without a valid reason for doing so. The above scenario could all be done dynamically with T-SQL in 1 stored procedure call. Thus several SQL statements execute with only 1 trip to the database as opposed to several.

Why T-SQL?

Static SQL, like you write in your ASP pages, has several drawbacks. The biggest being that it is static. With TSQL you can build your queries to get a high amount of reuse out your objects. Much like you would use IF statements and Select CASE statements in ASP program, you can do the same with T-SQL. The following is an example of a TSQL statement that selects a different field in the SQL Server Database based on the parameters passed to the Stored Procedure (Stored Proc):

CREATE PROCEDURE SP_Products
@cat int,
@Price nvarchar(10)
 AS

Select CODE,TITLE,Version,Status,

Case @Price
    When 'Price' THEN Price
    When 'PriceA' Then PriceA
    When 'PriceB' Then PriceB
    When 'PriceC' Then PriceC
End,
  Lots,
  LotsOf,
  Description,
  Pic
From Products
Where Category = @cat ORDER BY CODE

In the above example I am selecting a different Price field based on the user level of the buyer. Some additional benefits:

Faster Processing. Sure I could have used ASP to generate the same SQL query but at an expensive of extra data processing, combining technologies, and complexity to update.
Maintainability. Stored Procedures are essentially functions. Just like you build a function for maintenance, a stored procedure works the same way. It is an object-oriented approach to database programming at the database level. Not the application level (which is not an option regardless).
Security. Stored Procedure calls say nothing important to the observer. If you are concerned about who knows what about your database, you hide practically everything with stored procedures.
The Editor Itself. Editing stored procedures on the SQL server is also far more practical and user friendly than opening ASP files and re-writing static SQL. The SQL server also checks your syntax and will not let you write an invalid query. You do not have to keep hitting the refresh button in the browser until everything checks out.

So that's an overview of T-SQL. Any serious database programmer should be learning how to use it. Especially for large complex applications. Look for specific T-SQL examples soon.

Rob Taylor posts in the forums under the handle taylo. He is a consultant specializing in ASP and SQL Server. You can reach him at TConsult.