Introduction:
In this article we will look at some of the
basic T-SQL Queries. These T-SQL Queries are very commonly used in most of the
applications and I would like to point out that how easily some of these task
can be performed.
Returning String Instead of NULL:
Consider a situation in which your database
table contains NULL and you don't want to return NULL but some message. Like
suppose you have a Person table and a Phone Table and a person does not have a
phone number so we can easily return a message saying "No Phone Number Found"
instead of returning a NULL.
SELECT P.Name, 'PhoneNumber'
= CASE WHEN Ph.PhoneNumber IS NULL THEN
'No Phone Number Exists'
ELSE
Ph.PhoneNumber
END
FROM tblPerson P, tblPhone Ph
WHERE P.PersonID = Ph.PersonID |
The heart and soul of this simple query is the
CASE Statement where we check that if the field is NULL or not. If the field is
NULL we just replace it with a personal message. You can also have multiple WHEN
Statements checking for different conditions.
Update:
You can also use the ISNULL method of
the SQL SERVER 2000 to do the same operation. Check out the code below
which returns the same result.
SELECT P.Name, Ph.PhoneNumber, ISNULL(Ph.CellNumber,'No Phone Number') AS CellNumber
FROM tblPerson P, tblPhone Ph
WHERE P.PersonID = Ph.PersonID
Assigning Text to NULL Fields in Database
Table:
In this small query we will see how we can
assign text to the fields in the database that are NULL.
UPDATE tblPhone
SET PhoneNumber = 'No Phone'
WHERE PhoneNumber IS NULL |
TABLE DATATYPE in SQL SERVER 2000:
Did you know that there is TABLE DataType in
SQL SERVER 2000? Yes you can use it just like a TABLE and perform operations on
that. Let's take a look at a simple example.
DECLARE @MyTable TABLE(PersonID int , Name nvarchar(50) )
-- TABLE DATATYPe
INSERT INTO @MyTable(PersonID,Name)
SELECT PersonID,Name FROM tblPerson
SELECT * FROM @MyTable |
The above example just shows how you can create
a simple TABLE DataType and populate it with the Data from Person table.
FOR XML:
FOR XML can be used in different way
but the most flexible is FOR XML EXPLICIT in which the developer has
complete control over the returned XML. Let's take a look at a very
simple example:
SELECT 1 AS TAG,
NULL AS PARENT,
PersonID AS [Person!1!PersonID],
NAME AS [Person!1!NAME!ELEMENT]
FROM
tblPerson
FOR XML EXPLICIT
And this will generate the following XML:
Person
PersonID="38"><NAME>Azam</NAME></Person><Person
PersonID="39"><NAME>Saif</NAME></Person><Person
PersonID="40"><NAME>John</NAME></Person><Person
PersonID="41"><NAME>Marry</NAME></Person><Person
PersonID="42"><NAME>Neno</NAME></Person>
I hope you liked the article, happy coding!