Friday, November 07, 2014

TSQL Tutorial:Function ,Case , Data Type Conversion

Functions
Functions can be used to modify a column value in transit. Transact-SQL provides a bevy of functions that
can be roughly divided into six major groups: string functions, numeric functions, date functions,
aggregate function, system functions, and meta-data functions. Here's a Transact-SQL function in action:
SELECT UPPER(LastName), FirstName
FROM customers
FirstName
-------------- ---------
DOE John
DOE Jane
CITIZEN John
Guru’s Guide to Transact-SQL
8
Here, the UPPER() function is used to uppercase the LastName column as it's returned in the result set.
This affects only the result set—the underlying data is unchanged.

Converting Data Types
Converting data between types is equally simple. You can use either the CAST() or CONVERT() function to
convert one data type to another, but CAST() is the SQL-92–compliant method. Here's a SELECT that
converts the Amount column in the orders table to a character string:
SELECT CAST(Amount AS varchar) FROM orders
--------
123.45
678.90
86753.09
678.90
Here's an example that illustrates how to convert a datetime value to a character string using a specific format:
SELECT CONVERT(char(8), GETDATE(),112)
--------
19690720
This example highlights one situation in which CONVERT() offers superior functionality to CAST().
CONVERT() supports a style parameter (the third argument above) that species the exact format to use
When converting a datetime value to a character string. You can find the table of supported styles in the
Books Online, but styles102 and 112 are probably the most common.
CASE
CASE has two basic
forms.
In the simpler form, you specify result values for each member of a series of expressions that are
compared to a determinant or key expression, like so:
SELECT CASE sex
WHEN 0 THEN 'Unknown'
WHEN 1 THEN 'Male'
WHEN 2 THEN 'Female'
ELSE 'Not applicable'
END
In the more complex form, known as a "searched" CASE, you specify individual result values for multiple,
possibly distinct, logical expressions, like this:
SELECT CASE
WHEN DATEDIFF(dd,RentDueDate,GETDATE())>15 THEN Desposit
WHEN DATEDIFF(dd,RentDueDate,GETDATE())>5 THEN DailyPenalty*
DATEDIFF(dd,RentDueDate,GETDATE())
ELSE 0
END
A searched CASE is similar to an embedded IF...ELSE, with each WHEN performing the function of a new
ELSE clause.
Personally, I've never liked the CASE syntax. I like the idea of a CASE function, but I find the syntax
unwieldy.
It behaves like a function in that it can be nested within other expressions, but syntactically, it looks more
Like a flow-control statement. In some languages, "CASE" is a flow-control keyword that's analogous to
The C/C++switch statement. In Transact-SQL, CASE is used similarly to an inline or "immediate" IF—it
returns a value based on if-then-else logic. Frankly, I think it would make a lot more sense for the syntax
to read something like this:
CASE(sex, 0, 'Unknown', 1, 'Male', 2, 'Female', 'Unknown')
or
CASE(DATEDIFF(dd,RentDueDate,GETDATE())>15, Deposit,
DATEDIFF(dd,RentDueDate,GETDATE())>5, DailyPenalty*
DATEDIFF(dd,RentDueDate,GETDATE()),0)
This is the way that the Oracle DECODE() function works. It's more compact and much easier to look at

than the cumbersome ANSI CASE syntax.

No comments:

Post a Comment