Transact-SQL Programming Constructs - Chapter 7

SQL Server Functions
ANSI=89 SQL Allows standard arithmetic operators (+, -, *, /, and ^)
in both select and data modification statements.

/* display 10% of each price */
select title, price, price / 10
       from titles

/* use an operator in an update statement */
update titles
       set price = price * 1.1
       where type = "business"

/* perform concatenation with "+" */
select location = city + ', '+ state
       from publishers

SQL Server data functions provide advanced data manipulation, 
including the following:
.String functions
.Math functions
.Date functions
.System functions

String Functions
These functions enable manipulation, parsing, and conversion of 
character strings.

Function				Definition
----------------------------------------------------------------
			Length and Parsing

datalength (char_expr)			Returns the integer # of the
					characters in char_exp, ignoring
					any trailing spaces.


substring (expression,start, length)	Returns part of the string

right (char_exp, int_expr)		Returns int_expr characters from
					the right of char_expr

			Basic String Manipulation

upper (char_expr)			Converts char_expr to uppercase

lower (char_expr)			Converts char_expr to lowercase

space (int_expr)			Generates a string of int_expr spaces

replicate (char_expr, int_expr)		Repeats char_expr, int_expr times

stuff (char_exp1, start, length, char_expr2)	Replaces length characters
					from expr1 as start with expr2

reverse (char_expr)			Reverses text in char_expr

ltrim (char_expr)			Removes leading spaces

rtrim (char_expr)			Removes trailing spaces

			Conversions

ascii (char_expr)			Returns the ASCII value of the first
					character in char_expr

char (int_expr)				Converts ASCII code to character

str (float_expr [,length [,decimal]])	Numeric-to-character conversion

soundex (char_expr)			Returns the soundex value of char_expr

difference(char_expr1, char_expr2)	Returns the difference between the 
					soundex values of the expressions

			In-String Searches

charindex (char_expr, expression)	Returns the starting position of the
					specified char_expr, else zero

patindex ("%pattern%", expression)	Returns the starting position of the 
					specified pattern, else zero

-----------------------------------------------------------------------------


soundex

The soundex function is useful in determing whether character strings are likely
to sound similar. The function returns a value representing the first letter and
each of the significant consonant sounds, as in this axample.

select soundex ('marsupial')

M621  

(M r s p)

select name, "soundex " = soundex (name)
from smith_table

name            soundex
---------------------------
Smith		S530
Smyth           S530
Smithe		S530
Smythe		S530
Smithson	S532
Smithsonian	S532
Smithers	S536
Smothers	S536

select id, name
from smith_table
where sdx = soundex ("Smythe")

Searching Within Strings with charindex

select charindex (",", "red, white, blue") "First Comma Location"

First Comma Location
--------------------
4

Wildcards
SQL Server provides wildcards to enable pattern matching in text searches.

Wildcard	Description
----------------------------
%		Matches any quantity of characters, or no characters

_		(underscore) Matches any single character (a place holder)

[ ]		Specifies a range of valid characters, or an "OR" condition

-----------------------------

Within the brackets, a couple of operators are available. The caret (^) in 
the first position means NOT, so the pattern [YyNn] means "capital or 
lowercase Y or N" versus [^YyNn], which means "any character except capital
or lowercase Y or N."
	
[ABG]		/* matches A, B or G */
[A-CE-G]	/* matches A, B, C, E, F, or G */
[^ABG]		/* matches any except A, B, and G */
[^A-C]		/* matches any except A, B, and C */

The escape Clause			
To include wildcard characters as literals in a search string, use an 
escape character.

SQL Server, by default, uses the ([ ]) to escape a wildcard, which means that
you want to use the wildcard as a literal character.

/* find a string containing the value 20% */
select *
from test_tab
where description like "%20[%]%"

The ANSI-89 SQL standard defines the escape clause to specify an 
escape character:

like char_expression escape "escape_character"

/* find a string containg the value 20% */
select *
from test_tab
where description like "%20#%%" escape "#"

Using Wildcards with patindex
select patindex ("%[,.!]%", "red, white, blue") "First Punctuation"

First Punctuation
-----------------
4
* Note that the pattern as a whole must match the string, so it's typical
to include your search string in percent signs (%). The location of the 
first matching non-wildcard character is reported by the query.

String Function Examples

select au_lname + ", "+  au_fname
       from authors

---------------------------------
White, Johnson



/* replace characters within a string */
update authors
	set phone = stuff (phone, 4, 1, "-")

This update statement uses the stuff function to replace the fourth character
in each phone number with a hyphen (-).


select distinct type, datalength (type), datalength (rtrim(type))
	from titles

type
----------------------------------------------------
Undecided	12	9
Business	12	8
Popular_comp	12	12
Psychology	12	10


select title
	from titles
	where title like "%Computer%"

This displays all titles containing the word Computer, using the LIKE
operator and wildcards to identify many rows.

Mathematical Functions
SQL Server performs standard arithmetic operations using normal precedence:

. Functions and parentheses are evaluated first.
. Next, multiplication, division, and exponents.
. Finally, addition, subtraction, and logical operations.

All operations proceed from left to right.

* SQL Server doesn't include a built-in arithmetic operator for exponents.
Instead, exponents are derived with the power() function. For example, to
raise 2 to the 12th power, use:

select power (2, 12)

* Be careful when using the power() function for advanced work. The result
of the power() function is of the same datatype as the first parameter passed.

select power (2, 0.5)  --> 0


select power (2., .5)  --> 1.

select power(2.000, 0.5)  --> 1.414)

declare @base numeric (14, 9)
select @base = 2
select power (@base, .5)

SQL Server also supports the standard trigonometric functions and a number of
other useful ones.

Function		Description
-------------------------------------------------------
abs (numeric_expr)	Absolute value 

ceiling (numeric_expr)	Smallest integer greater than or equal to

exp (float_expr)	Exponential value of the specified value

floor (numeric_expr)	Largest integer less than or equal

pi ()			Returns the constant value of 3.1415926..

power (numeric_expr, power)	Returns the value of numeric_expr
			to the power of power.

rand  (int_expr)	Returns a random float number between o and 1

round (numeric_expr, int_expr)	Rounds off a numeric expression to the 
				precision specified in int_expr.

sign (int_expr)		Returns the positive (+1), zero (0), or negative (-1)

sprt (float_expr)	Returns the square root of the specified value.


Date Functions
 
SQL Server includes date functions to perform date parsing and date arithmetic.

Function			Description
---------------------------------------------------------
getdate ()			Return the current system date and time

datename(datepart, date_expr)	Returns the specified part of date_expr
				value as a string, converted to a name
				(for example, June) if appropriate

datepart (datepart, date_expr)	Returns the specified part of the date_expr
				value as an integer

datediff (datepart, date_expr1,
	   date_expr2)		Returns date_expr2 minus date_expr1,
				as measured by the specified datepart

dateadd (datepart, number, date_expr)	Returns the date produced by adding
					the number of the specified datepart
					to date_expr

Date Parts

date Part	Abbreviation		Value Range (in datepart)
-----------------------------------------------------------------
year		yy			1753-9999

quarter		qq			1-4

month		mm			1-12

dayofyear	dy			1-366 

day		dd			1-31

week		wk			1-54

weekday		dw			1-7 (1-Sunday)

hour		hh			0-23

minute		mi			0-59

second		ss			0-59

millisecond	ms			0-999
------------------------------------------------------------------

/* what is the current date and time */
select getdate()

Jan  26 1998  7:30PM


/* how old are unpaid invoices? */
select invoice_no,
	datediff (dd, date_shipped, getdate()) "invoice aging"
	from invoices
	where balance_due > 0


/* what month were these books published? */
select title, datename (mm, pubdate)
	from titles

System Functions
See SQL Server system functions (page 192)

compute and compute .. by
Not ANSI Standard  (many organizations don't use it)

isnull

total_order
------------
100
50
25
(null)

/* average of orders excluding nulls */
select avg (total_order) "avg"
	from invoices

avg
---------
58

/* average of orders using 0 for orders
** that have a null total */
select avg (isnull (total_order, 0)) "avg using 0"
	from invoices

avg using 0
----------
43


SQL Server also provides a nullif function that sets a value equal 
to null if two expressions are equivalent. 

create table price
	(item int not null,
	price money not null)

Certain rows may contain a price of -1, indicating that the item is not
priced:

insert prices values (1, $10)
insert prices values (2, $25)
insert prices values (3, -$1)  -- means no price (nulls not permitted in column)


Values of -1 should not be included in averages.

select avg(price) "incorrect average",
	avg (nullif(price, -1)) "correct average"
	from prices

incorrect average		correct average
------------------------------------------------
11.33				17.50

coalesce
The coalesce statement allows you to return the first non-null
expression in a list:

coalesce (expression1, expression2,...,expressionN)

select name, homephone, workphone,
	coalesce (homephone, workphone) as "phone number"
	from telephones

name	homephone	workphone	phone number
--------------------------------------------------------
A	(null)		1		1
B	3		5		3
C	(null)		7		7

Programming Constructs
Transact-SQL provides a set of programming constructs to help
those writing stored procedures and triggers to write structured, 
modular code that's resident at the database server.

. Batches
. Comments
. Local and global variables
. Message handling
. Error handling
. While loops
. if .. then loops
. begin...end loops

Batches
A batch is the entire packet that's passed from the client to the server.
A batch can consist of only one SQL statement or it may contain several
statements. Statements in a batch are passed as a group, compiled as a 
group, and executed as a group. This means that none
of the statements in the batch is executed if there are any syntax errors 
in the batch.

/* this batch performs two selections */
select * from authors
select * from titles
go


/* this batch setes security on a table */
grant select, insert to mary on mytable
revoke select, insert from mary on mytable (col_3)
go

* go is not T-SQL. it's an instruction to the front end to transmit to the 
  server everything that precedes go.


Certain errors occur during execution. When a fatal execution error is
encountered, the batch stops processing and only the statements that preceded
the error are executed. Most errors don't stop batch processing.


Sometimes the successful execution of only a part of the batch is disastrous.
In that case, you need to use transaction control statements to group a set
of statements together as a single logical unit of work and ensure that all
the statements execute successfully-or none do.

begin transaction
	insert tiny (c1) value (1)
	if @@error != 0 goto error_handler
	insert tiny (c1) value (-1)
	if @@error != 0 goto error_handler
	insert tiny (c1) value (12)
	if @@error != 0 goto error_handler
	commit transaction
return

error_handler:
	rollback transaction
return

Comments
Comments can be included in batches, and it's a good practice to include
description comments in stored procedures.

There are two forms of comments:
Multiple-line comments bracketed with /* and */
single-line comments identified with two hyphens (-).

Local Variables
Variables for use in a batch or stored procedure are defined with the declared
statement.

declare @last_error int

/* declare two variables in a single statement */
declare 
	@name varchar (30)
	@type int

Local variables exist only for the life of the batch, stored procedure,
or trigger where they are declared. When the batch is complete, all
information stored in the local variables is lost.

Assigning Values to Local Variables 
SQL Server provides two methods of assigning local variables.

The most common method of assigning values is a select statement, generally
referred to as an assignment select.

select @variable_name = expression [,...]
[from...
[where ...]]

/* set a variable equal to a constant expression */
declare @int_var int
select @int_var = 12
go

Until you assign a value to a local variable, the value of the variable 
is null.

declare @counter int
select @counter = 0
while @counter < 10
	begin
		select @counter = @counter + 1
		select @counter
	end

/* multiple variables set in a select statement */
declare @single_auth varchar (40),
	@curdate datetime
select @single_auth = au_lname,
	@curdate    = getdate()
	from authors
	where au_id = '123-45-6789'


Assigning Local Variables with update

declare @qty smallint
update sales
	set @qty = qty = qty * 2
	where title_id = "BU1032"
		and stor_id = "6380"
		and ord_num = "6871"

select @qty as "New Quantity"	


Global variable
Global variables are used by the server to track server-wide and 
session-specific information. Global variables can't be defined by users
and aren't used to pass information across processors by applicants.


Examples,

Function		Description
-------------------------------------------------
@@rowcount		Number of rows processed by the preceding command
@@error			Error number reported for the last SQL statement


print
The print statement is used to pass a message to the client program's 
message handler. Messages can include up to 255 characters of text.
You can pass only a literal character string or a single character-type
variable to print.

print {character_string | @local_variable | @@global_variable}

/* send a string to the message handler */
print "This is a message "

This is a message

/* send a variable to the message handler 
** the variable needs to be of a character type */
declare @msg varchar (30)
select @msg = "Hello,  " + user_name ()
print @msg

Hello, dbo

Conditional Execution: if ... else
Statements to be executed conditionally are identified with the if...else
construct.

if boolean_expression
	{statement | statement_block}
[else
	{statement | statement_block}]

/* check for average price of business books
** and return a message */

if (select avg(price) from titles
   	where type = "business") > $19.95
   print "The average price of business books is greater than $19.95"
else
   print "The average price of business books is less than $19.95"

if exists
The if exists test is used to check for the existence of data, without 
regard to the number of matching rows.

if [not] exists (select_statement)
	{statement | statement_block}
[else
	{statement | statement_block}]

/* check for authors named Smith */
declare @lname varchar (40), @msg varchar (255)
select @lname = "Smith"
if exists (select * from titles
	   where au_lname = @lname)
   begin
	select @msg = "There are authors named " + @lname
	print @msg
   end
else
   begin
	select @msg = "There are no authors named " + @lname
	print @msg
   end
go

Statement Blocks: begin...end

To treat multiple SQL statements as a single block, use the begin...
end construct:

begin
	SQL statements
end

Repeated Execution: while
while boolean_condition
	[{statement | statement_block}]
[break]
[continue]



break unconditionally exits the while loop and continues processing
with the first statement after the end statement.

continue reevaluates the Boolean condition and begins processing from
the top of the loop if the condition is true.

/* loop until average price equals or exceeds $25 */
while (select avg (price) from titles ) < $25
	begin
		update titles set price = price * 1.05
		/* if fewer than 10 books are less than 
		** $15, continue processing */
		if (select count (*) from titles )< 10
			continue
		else
		/* If maximum price of single book exceeds
		** $50, exit loop */
		   if (select max (price) from titles ) > $50
			break
	end


Repeated Execution: goto
goto is provided to allow you to continue processing starting at a
labeled statement. Here's the basic syntax:

goto label
...

label:

Although structured programming avoids using goto statements because
of the syntactic problems it can create. here's a simple example that
uses a goto statement to simplify error handling:

begin transaction
	insert tiny (c1) value (1)
	if @@error != 0 goto error_handler
	insert tiny (c1) value (-1)
	if @@error != 0 goto error_handler
	insert tiny (c1) value (12)
	if @@error != 0 goto error_handler
	commit transaction
return

error_handler:
	rollback transaction
return

Event Handling: waitfor
The waitfor statement is used to cause a query to pause for a period of time
or until an event occurs.  SQL Server puts the process on the sleep queue and
awakens it when the event occurs.

waitfor {delay "time" | time "time"}

delay pauses for the specified amount of time. time waits until the specified
time of day.  The time specified for delay and time is in hh:mm:ss format-
time can't exceed a 24-hour period, and you can't specify variable.

/* Pause until 10pm */
waitfor time "22:00:00"

/* display current logins every 30 seconds */
while 1 < 2
	begin
		waitfor delay "00:00:30"
		exec sp_who
	end

return
To exit a batch, stored procedure, or trigger unconditionally, use the
return statement.  In this example, return is optional and has no real effect:

select * from authors
print "finishing now"
return

return is often used with if statements to allow the server to end a batch
when a condition is identified:

/* return can be used with a conditional statement 
** to terminate processing */
if not exists (select * from inventory
		where item_num = @item_num)
	begin
		raiserror 51345 "Not found"
		return
	end
print "No error found"
return 

set Options

Options affect the way the server handles specific conditions. Options
only exist for the duration of your connection, or for the duration of a 
stored procedure or trigger if the are set in that context:

set condition {on | off | value }

/* instruct the server to return only the first 100 rows of data */
set rowcount 100

/* tells the server to stop reporting the number of rows returned */
set nocount on

/* asks the server for the final optimization plan for the query */
set showplan on

Cursor
ANSI-SQL provides the capability of addressing a set of rows individually,
one row at a time, by a cursor. A cursor is a pointer that identifies a 
specific working row within a set.

* Cursor introduce a staggring performance penalty in your applications and 
nearly always need to be avoided.

Last update January 26, 1998.