Split a string into records based on delimiter, SQL Server 2005

If you have a string which is separated with some delimiter, say comma. And if you think you need to split the string based on the delimiter and return as a recordset, then this function is for you.

This function will split a string into records based on the delimiter

Usage:

SELECT * FROM dbo.SplitStr('Bangalore,Kerala,Mumbai',',')

The result will be

Bangalore
Kerala
Mumbai

This may be helpful if you want to use this in queries like

Select * from Table1 Where City in (SELLECT * FROM dbo.SplitStr('Bangalore,Kerala,Mumbai',','))

Which will return you all the records where the City is either Bangalore, Kerala or Mumbai like in our example...

Function : SplitStr

List all Stored Procedures,Tables or Views in SQL Server 2000/2005

Some times we need to take a print of all the stored procedures, tables or views from our database in SQL Server 2000/2005. For that purpose first we need to list all the user created stored procedures,tables or views. You can make use of the following queries.

1. To List all user created Stored Procedures
Select name From sys.objects Where type = 'P'

2. To List all User created Tables
Select name From sys.objects Where type = 'U'

3. To List all User Created Views
Select name From sys.objects Where type = 'V'

4. You can retrieve data by giving your own filter values, just list all columns to find what is needed
Select * From sys.objects

Now you can create interesting queries for various analysis or just for information purpose
Example:
Get me all the user stored procedures that are created after 20th jan 2009
Select name From sys.objects Where type = 'P' And create_date > '1/20/2009'

So now you can have your own queries...
:)

Note:
In SQL Server Query Analyser (SQL Server 2000) or SQL Server Management Studio Express (SQL Server 2005), you can save the result of the sql query to a file or can view as text.

Right click in the query window and select "Results To". Now you have three options
1) Results to Text, 2) Results to Grid, 3) Results to File
Select your choice.

Dealing with null in sql queries in SQL Server 2000/2005

We may need to deal with null values often when we write queries in SQL Server 2000/2005. Here I provide some examples that are commonly used. One of my friend asked how to check whether a field is null, he tried col1=null but didn't worked. So I thought I will share this small but useful examples to all.

1) To check whether a field/column value is null
Just check if it is null

Eg:
select * from tbl where col1 is null;

2) To check whether a field/column value is not null
Just check if it is not null

Eg:
select * from tbl where col1 is not null;

3) To set a field/column value to null (update a field value to null)
Just set it to NULL

Eg:
update tbl set col1= NULL Where val1 = 1;

Another important Note:
When we want to take the maximum value (max) or the total(sum) using aggregate functions, it is always good to check for null values.If the table do not have any records or the result of the query is null, this may result in error.

select @nextVal = max(col1) + 1 from table1; --Not preffered way

So we will try
select @nextVal = max(isnull(col1,0)) + 1 from table1; --Not always success
Hey, this is also not working...

Ok finally, the right way
select @nextVal = isnull(max(col1),0) + 1 from table1;

To find the sum
select @Total = isnull(sum(col1),0) from table1;

Happy Coding ....
:)