Skip to main content

USEFUL T-SQL QUERIES

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

Comments

Post a Comment

Popular posts from this blog

World Environment Day

This entry is in response to the Conserve, Recycle and Discover contest organised by blogadda.com in association with Pringoo ! World Environment Day, commemorated on 5 June since 1972, is one of the ways focuses world attention on the environment and encourages political action. People from countries all over the world have mobilized for individual and organized environmental action. Activities involve all sectors of society, governments, non- and inter-governmental organizations, businesses, industries, civil society, media and schools. Please take the following Pledge to save our environment. 1, I pledge to Recycle more, use less water and electricity. 2, I pledge to smoke less in 2010. 3, I pledge to send more electronic documents instead of using paper. 4, I pledge to continue using my bicycle to go to work (instead of my motorbike). 5, wash with full loads it is more energy efficient than doing several smaller ones. 6, Incite of dryer I will use hang dry for energy sav...
1 comment
Read more

GridView Alphabet Paging

Introduction: GridView paging feature allow us to display fixed number of records on the page and browse to the next page of records. Although paging is a great feature but sometimes we need to view all the items alphabetically. The idea behind this article is to provide a user with a list of all the alphabets and when the user clicks on a certain alphabet then all the records starting with that alphabet will be populated in the GridView control. Populating the GridView Control: The first task is to populate the GridView control. I will be using the Northwind database in my article which, is installed by default for SQL SERVER 2000 and SQL SERVER 7 databases. The code below is used to populate the GridView control. private void BindData() { string connectionString = "Server=localhost;Database=Northwind;Trusted_Connection=true"; SqlConnection myConnection = new SqlConnection(connectionString); SqlDataAdapter ad = new SqlDataAdapter("SELECT P...
1 comment
Read more

TED

TED is a nonprofit devoted to Ideas Worth Spreading. It began in 1984 as an annual conference devoted to Technology, Entertainment and Design -- hence TED -- but TED's reach, and its scope, have become ever broader since then. TEDTalks cover science, arts, politics, global issues, architecture, music and more. Speakers come from a wide variety of communities and disciplines -- people like Bill Clinton, Nobel laureate Murray Gell-Mann, Wikipedia co-founder Jimmy Wales, and Google co-founders Sergey Brin and Larry Page. The TED Conference itself takes place in Long Beach each spring, with a simulcast event in Palm Springs. In 2009, we'll also host a summer conference in Oxford, UK, called TEDGlobal, as well as a conference in fall 2009 called TEDIndia, in Mysore, India. Anyone can apply to attend a TED Conference! If you want to come, apply for an invitation. More info here: http://www.ted.com/conferences
Post a Comment
Read more