SQL - Dynamic Order By clause

Have you ever wanted a stored procedure to order it's results dynamically? The ORDER BY clause is the place to start but normally relates to a series of columns that's hard-coded. If you want this clause to change depending on some input, you can adapt the following SQL. It contains a switch statement that compares the value of a parameter against pre-defined string column names. When a match is found, the respective clause is inserted. You can also specify the direction (ascending/descending) which doubles the amount of case options.

declare @orderBy nvarchar(max)
set @orderBy = 'columnName'
 
-- 0 Descending
-- 1 Acsending
declare @orderByDirection int
set @orderByDirection = 0
 
select *
from tableName
order by
    case when @orderBy = 'columnName' and @orderByDirection = 0
        then columnName end desc,
    case when @orderBy = 'columnName' and @orderByDirection = 1
        then columnName end

SQL Server compatability level

When you upgrade to SQL Server 2005, you may find that some SQL statements are no longer valid and produce errors. A typical example is when you have 2 tables under different databases with the same name (the schema is irrelevant).

Assume that you have 2 databases, MyDatabase1 and MyDatabase2, each containing a table called MyTable. Both tables have a primary key called ID which are also foreign keys between Database1..MyTable and Database2..MyTable. The following SQL statement is valid under SQL Server 2000 but invalid under SQL Server 2005:

use [MyDatabase1]
 
select * from [MyTable]
inner join [Database2]..[MyTable] on [MyTable].[ID] = [Database2]..[MyTable].[ID]

SQL Server 2005 is more strict and won't allow you to reference MyTable of MyDatabase1 in this way, even though you've specified the default database (use [MyDatabase1]). Instead you'd have to change the statement:

select * from [Database1]..[MyTable]
inner join [Database2]..[MyTable] on [Database1]..[MyTable].[ID] = [Database2]..[MyTable].[ID]

Alternatively, you can change the compatability level on the nececssary database(s). This is a quick fix and should only be done if you can't easily modify the SQL code.

1. Open Microsoft SQL Server Management Studio
2. Expand the 'Databases' node
3. Right click the database and choose 'Properties'
4. Go to 'Options' and change the 'Compatability level' accordingly

The 3 compatability levels are:

• SQL Server 7.0 (70)
• SQL Server 2000 (80)
• SQL Server 2005 (90)

Stored procedure parameters - Default values

SQL Server stored procedures are good for separating data logic - I like to keep all things SQL together in one resource. I don't like using inline code because it can easily become out-of-date and requries more effort if several applications connect to the database(s). The only problem I had with stored procedures is that I ended up with numerous variations of the same thing (mainly selects). Using default parameters, it is possible to have a single select stored procedure that will always return the same data but the criteria is dynamic.

This example will use the following table:

User ( ID uniqueidentifier, Username nvarchar( 50 ), Password nvarchar( 50 ) )

The stored procedure has 3 parameters; @ID, @Username and @Password mapping to the 3 columns (ID, Username and Password) respectively. The default value of null means that they are optional parameters meaning you can specify none or a combination. Any value(s) passed in will be used in WHERE clause of the SELECT statement. Values of NULL (default) have no effect on the criteria because of the ISNULL check.

Example:

select * [User]
where [ID] = isnull( @ID, [ID] )

If @ID is NULL, the value from the ID column will be used; basically checking if it's equal to itself and returning all of the data inside the table. When the value isn't NULL, only row(s) containing that ID will be returned.


Stored procedure:

create procedure [SelectUser]
 
@ID uniqueidentifier = null
@Username nvarchar( 50 ) = null
@Password nvarchar( 50 ) = null
 
as
 
select * from [User]
where [ID] = isnull( @ID, [ID] ) and [Username] = isnull( @Username, [Username] ) and [Password] = isnull( @Password, [Password] )

Split SQL statements in C#.NET

Have you ever wanted to split a series a SQL statements? Probably not, but here is a code sample anyway! It works by looping through the string and splits the statements on semi-colons (only if they aren't part of some text). I used this on a website that executed SQL code where I wanted to show feedback for each query.

using System;
using System.Collections.Generic;
 
public class StringUtility
{
    public static string SplitSQLStatements( string sql )
    {
        List<string> sqlStatements = new List<string>();
 
        char separator = ';';
 
        bool isQuoted = false;
 
        int substringStartIndex = 0;
 
        if ( sql.Contains( ";" ) )
        {
            for ( int characterIndex = 0; characterIndex < sql.Length; characterIndex++ )
            {
                if ( sql[characterIndex] == '\'' )
                {
                    isQuoted = !isQuoted;
                }
                else if ( sql[characterIndex] == separator && !isQuoted )
                {
                    sqlStatements.Add( sql.Substring( substringStartIndex, characterIndex + 1 - substringStartIndex ).Trim() );
 
                    substringStartIndex = characterIndex + 1;
 
                    if ( sql.IndexOf( separator, substringStartIndex, sql.Length - substringStartIndex ) == -1 )
                    {
                        break;
                    }
                }
            }
        }
 
        if ( !sql.EndsWith( separator.ToString() ) )
        {
            sql = string.Format( "{0};", sql );
        }
 
        if ( substringStartIndex < sql.Length - 1 )
        {
            sqlStatements.Add( sql.Substring( substringStartIndex, sql.Length - substringStartIndex ).Trim() );
        }
 
        return sqlStatements.ToArray();
    }
}

SQL - Capitalise function

This function will capitalise the first letter of every word in a sentence. The commented line can be used to ignore single letter words.

create function dbo.Capitalise
(
    @Text varchar( 1000 )
)
 
returns varchar( 1000 )
 
as
 
begin 
    declare @ReturnString varchar( 1000 )
    set @ReturnString = ''
 
    declare @Word varchar(30)
 
    declare @Pointer int
    set @Pointer = 0
 
    if right( @Text, 1 ) <> ' '
        set @Text = @Text + ' '
 
    while charindex( ' ', @Text, @Pointer ) > 1
    begin
        set @Word = substring( @Text, @Pointer, charindex( ' ', @Text, @Pointer ) - @Pointer )
 
        --if len( @Word ) > 1
            set @Word = upper( left( @Word, 1 ) ) + lower( right( @Word, len( @Word ) - 1 ) )
 
        set @Pointer = charindex( ' ', @Text, @Pointer ) + 1
        set @ReturnString = @ReturnString + @Word + ' '
    end
 
    set @ReturnString = rtrim( ltrim( @ReturnString ) )
 
    return @ReturnString 
end

Example:

select dbo.Capitalise( 'this is a test' ) as CapitalisedSentence