1 / 17

Transact-SQL

Transact-SQL. What Is It?. Transact-SQL is a programming language It runs under SQL Server It extends SQL by adding many programming constructs It only works with MS SQL Server (and Sybase). Added Features. Variable definitions BEGIN…END IF THEN … ELSE WHILE CASE PRINT.

shamus
Download Presentation

Transact-SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Transact-SQL

  2. What Is It? • Transact-SQL is a programming language • It runs under SQL Server • It extends SQL by adding many programming constructs • It only works with MS SQL Server (and Sybase)

  3. Added Features • Variable definitions • BEGIN…END • IF THEN … ELSE • WHILE • CASE • PRINT

  4. Arithmetic Operators • + -- Addition • - -- Minus • * -- Multiply • / -- Divide • % -- Modulo

  5. Comparison Operators • = -- Equal • > -- Greater Than • < -- Less Than • <= -- Less Than or Equal To • >= -- Greater Than or Equal To • <> -- Not Equal

  6. Variables • Variables are defined by the DECLARE • Variable names begin with @ • They are local in scope • They must have a data type defined • They follow the same convention as columns

  7. Variable Examples • DECLARE @limit money • DECLARE @lname char(20) • DECLARE @x int • DECLARE @limit money, @lname char(20)

  8. Set Command • The value of a variable can be assigned using the SET command • SET @limit = $10 • SET @limit = @limit + $5 • SET @min_range = 0, @hi_range = 100

  9. Using Variables in Selects Declare @firstname varchar(30) Select @firstname = au_fname From authors Where au_lname = ‘Greene’ Select @fname (but…)

  10. Using Variables Select @firstname = au_fname From authors Where au_lname = ‘Delaney’ Select @fname Returns the same first name as the previous query. There is no Delaney in the database so @fname is left unchanged.

  11. Using Variables • Variables can also be used in: • Updates • Deletes • Inserts

  12. BEGIN…END • Used for Blocking Statements Together • Typically Used with IF or WHILE constructs • Similar to {…} found in C and C++

  13. IF THEN…ELSE • Standard Conditional checking statement • Found in many languages

  14. CASE • CASE is seen in other languages as well • It can be thought of as a series of IF THEN … ELSE statement • It can have several WHEN clauses to test for specific conditions • It can have 1 ELSE clause if none of the WHENs are satisfied

  15. WHILE • Performs a standard WHILE loop • Remember a WHILE does the check at the top of the loop • BREAK will immediately exit the inner most WHILE loop • CONTINUE restarts the loop at the top

  16. PRINT • Will display any character string up to 8000 characters • A PRINT will only display character • Use CAST or CONVERT • A PRINT will only display 1 logical variable • Use concatenation

  17. Cast • Changes the value from one data type to another • CAST (original_expression as datatype)

More Related