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">
Comments