Transact-SQLBy 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:
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.
|
- Advertisement - |