Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to SQL. SQL, often expanded to Structured Query Language, is a standardized computer language that was originally developed by IBM for querying, altering and defining relational databases, using declarative statements. T-SQL expands on the SQL standard to include procedural programming, local variables, various support functions for string processing, date processing, mathematics, etc. and changes to the DELETE and UPDATE statements. These additional features make Transact-SQL Turing complete.
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.
Maintainability.
Security.
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.
