transact sql the language of microsoft sqlserver copyright © 2012 – 2014 by curt hill

Transact SQL

The language of Microsoft SQLServer

Copyright © 2012 – 2014 by Curt Hill


SQL

• Most of the standard SQL is contained in the Data Definition Language and Data Manipulation Language

• Much, but not all, has been covered

• The problem is that can we do what we want to do with just this?

• Generally there is more programming going on

Example• When you contact an online travel

agency several things happen• The origination and destination cities are

given as well as dates and number flying• System then searches for possible

carriers• Gives the user choices of flights• Once chosen these are booked• Receipts and the like are emailed to user

• This has to be done by a program and only part of this program generates SQL


Approaches

• How do we do the previous or similar tasks involving one or more databases?

• Three possibilities:– Embedded SQL – Generated SQL– Database Programming Language


Embedded SQL• A program written in a non-

database programming language handles this– Java, C++, COBOL or any other

general purpose language

• In the course of execution it issues SQL commands then modifies and displays the results– SQL commands are fixed strings in

the program

• This is usually client sideCopyright © 2012 – 2014 by Curt Hill

Generated SQL• Similar to the above except the SQL

commands are not fixed strings– Above languages as well as JavaScript,

Perl, PhP

• The program generates them– Fills in tables, fields, and comparison

constants depending on the need

• This could be on client side, server side or in-between (middleware)

• ERPs, CRMs and the like usually this


Database Programming Language

• Do everything in extensions to standard SQL

• Language must include comments, operators, variables, flow of control, functions and the like

• General purpose languages may construct the types needed for a database

• Database Languages have them built in


Transact SQL

• A Database Programming Language supported by Microsoft SQLServer– Most databases have similar

language

• This presentation then looks at the rudiments of programming such a beast


Comments

• Comments are ignored by SQLServer but allow people to read the code more easily

• Two types• Multiline is like C

– Starts with /* and ends with */

• Single line – Starts with two dashes -- and ends at

the end of line


Operators• We have seen most of these

• + addition or string concatenation• - subtraction• * multiplication• / division

• % division for remainder or as a wildcard in a Like

• | bitwise OR• & bitwise AND• ~ bitwise NOT• Comparisons: = < > <= >= <> !> !

< !=Copyright © 2012 – 2014 by Curt Hill

Predefined Variables• These represent available server

values • They always start with two @ • @@cpu_busy and @@idle amount

of time used since last started• @@error – the error number of the

last statement, zero means none• @@rowcount – the number of rows

affected by the last statement• @@total_errors – disk read/write

errors since startCopyright © 2012 – 2014 by Curt Hill

Variables• Variables must be declared using a

declare statement• Variables must be named starting

with a single @• Declaration syntax:declare @varname type– Type is any type you can use in a create

table

• Multiple variables can be done in one Declare, separated by commas

• Initialization may use a trailing = value


Display

• We display variables like we display anything else, by specifying in a select

• A Select that does not need any tables does not need a From clause or others

• We are now ready for our first program


Assignment statements• How do we change a variable’s

value?• With the set statement• Format:set @var = expression

• Where– Set is required reserved word– @var is the variable to be changed– Expression is a constant, variable or

variables and constants combined with operators


Simple Display


-- Simple junky programdeclare @curt int, @snerd2 bitset @curt = @@CPU_BUSY + @@idle/* Declares do not have to precede executables*/declare @snerd intset @snerd = @curt*2select @curt, @snerd, @@CPU_BUSY, @@IDLE

In Management Console


Cursors• You might think we could declare a

variable of type table and then assign a query result to that table

• That is not the way it is usually done• Most languages provide a cursor into

the resulting query• The cursor looks at only one row• However it may be moved forward or

backward – We may also move to a specific

numbered row


About Cursors• Although cursors are variables they

do not use the @ convention for names

• Their declaration is a little different as well

• Cursors are not general, but instead are tied to one specific query

• When we are done with them we release them – This frees the resources connected

with themCopyright © 2012 – 2014 by Curt Hill

Declaration

• The format for a cursor is:DECLARE cur_name CURSOR FOR query

• Where– Declare, cursor and for are reserved– Cur_name is the name of this cursor– Query is a Select with From and as

many options as you want• Into is not allowed


Cursor actions• Open

– The parameter is the cursor– This is when the query is executed

• Close – Closes a cursor but retains the data to

be reopened

• Fetch – See next slide• Deallocate

– Releases the cursor and the query values

– Parameter is cursorCopyright © 2012 – 2014 by Curt Hill

Fetch• Determines which row to get from

the query results• Format:Fetch direction From cursor into @var1, @var2 … @varn

• Where• Fetch, from, into are reserved words• @var1…@varN are local variables to

receive values• Direction is next, prior, first, last,

absolute n or relative nCopyright © 2012 – 2014 by Curt Hill

Fetch Again• When the cursor is open the next

one is the first one– You may start anywhere and scroll

forward of backward

• The global variable @@Fetch_status contains and integer result – Value -1 means the fetch failed– Value -2 means row is no longer

available

• If the value is negative then the variables are unchanged


Rows

• @@rowcount does not work for cursors, only for queries

• @@cursor_rows will show the number of rows as soon as the cursor is opened


Example


use collegedeclare fac_curse cursor for select f_naid, f_name, f_age, f_years from faculty where f_age > 40open fac_cursedeclare @naid int, @name varchar(20), @age int, @years intfetch next from fac_curse into @naid, @name, @age, @years…deallocate fac_curse

Notes

• The number of variables given in the Fetch should match the number of items in the Select

• If the into and variables are left off, we get output like a select


Flow of Control

• We will look at two flow statements– If – a decision statement– While – a looping statement

• Before this we need to consider blocks

• SQL uses a Pascal-like BEGIN and END

• Begin and End wrap a group of statements – Makes them into a block


If

• Format 1:if (condition) statement

• Format 2:if(condition) statementelse statement


Notes

• The condition may use the normal comparison operators with local variables and constants

• Either statement may be a BEGIN/END block

• If there is no else, it is the same as else do nothing


While

• Format:while (condition) statement

• The statement may be a single statement or a BEGIN/END block


Display

• Select sends output to the same place as a query result

• We also have a print command• This sends output the messages• The messages usually show how

many rows were affected or errors• Print may only show one string

– This may be the concatenation of several


Example Program

• Most things that you can do in a program, you could also do with just queries– It may just be harder

• In this program we will look at all faculty older than 40

• In this group we will compute the ratio of years of service to age

• Display the least and greatest


Setup


use collegedeclare fac_curse cursor for select f_name, f_age, f_years from faculty where f_age > 40open fac_cursedeclare @name varchar(20), @age int, @years real, @ratio real, @least_ratio real=1, @most_ratio real = 0, @least_name varchar(20), @most_name varchar(20)

The Loop – First Half


fetch next from fac_curse into @name, @age, @yearswhile (@@FETCH_STATUS <> -1) Begin set @ratio = @years/@age if (@ratio < @least_ratio) begin set @least_ratio = @ratio set @least_name = @name end if (@ratio > @most_ratio) begin set @most_ratio = @ratio set @most_name = @name end

The Loop – Last Half


if (@ratio > @most_ratio) begin set @most_ratio = @ratio set @most_name = @name end select @name, @age, @years, @ratio fetch next from fac_curse into @name, @age, @years end

Finish Up


deallocate fac_curseprint 'Least: ' + @least_name + ' ratio: ‘ + convert(varchar(20), @least_ratio)print 'Most: ' + @most_name + ' ratio: ' + cast(@most_ratio as varchar(20))

Oracle Addendum

• All the features that are present in TransactSQL are available in Oracle’s PL/SQL

• The syntax is enough different that substantial conversion would be needed

• An example follows, but does not represent a working program with everything properly defined


Oracle Examples


DECLARE part_number NUMBER(6); bonus int := 0; BEGIN part_number := 2135;IF part_number > special THEN bonus := (sales - quota)/4; ELSE bonus := 50; END IF; WHILE bonus < 100 LOOP bonus := bonus + 10; count := count + 1; END LOOP; END;

Finally

• A decent reference to Transact SQL is greater than 150 pages– This does not include pre-defined

stored procedured

• Clearly there is more to know• However, this shows many of the

fundamentals and basic flavor


transact sql the language of microsoft sqlserver copyright © 2012 – 2014 by curt hill

Documents