*

SQL Server Tips

From the Database Community Forum

July 2004

Dynamically ordering your result sets at run-time in T-SQL

As you probably have learned by now, T-SQL doesn't allow you to use the contents of a variable in an ORDER BY clause. But what if you want the flexibility to change the ORDER BY clause at run-time? Although it's not the most efficient way to construct queries, you can use dynamic SQL execution to accomplish this goal. Take a look at the following example:

-- Dynamic ORDER BY

DECLARE @OrderByColumnInt int
   SET @OrderByColumnInt = 3

   DECLARE @OrderByColumnName varchar(100)
   SET @OrderByColumnName = 'LastName'

   DECLARE @Sql varchar(8000)

-- ORDER BY with an ordinal column position

SET @Sql = 'SELECT * FROM Employees ORDER BY ' + 
CONVERT(varchar(10), @OrderByColumnInt)
   -- ORDER BY with a column name
   SET @Sql = 'SELECT * FROM Employees ORDER BY ' + 
@OrderByColumnName

   EXEC(@Sql)

As you can see here, the column to ORDER BY can be indicated either by its ordinal position in the resultset, or by the actual column name. Depending on how flexible and generic you want your application to be, you can use either approach. This technique is also suitable for use in a stored procedure.

Beware the dark side of column aliases in SQL Server

[Yes, the Jedi strength flows from the force, but beware the dark side.]

As you probably know, T-SQL allows you to use aliases for columns in your queries. For example, something like the following is perfectly valid:

SELECT FirstName + ' ' + LastName AS FullName

Aliasing can be especially useful as a means to provide a readable and sensible column name for an otherwise unnamed (calculated) column. But there's a dark side to relying on column aliases. Not only do they promote the potential use on nonstandard names (you might alias the concatenation above as FullName in one stored procedure but as CustomerName in another), there's an even greater potential for trouble: You can't refer to these aliases in WHERE, GROUP BY, or HAVING clauses. In these clauses, you can only refer to actual column names in the tables named in your FROM clause. This doesn't mean you should never uses column aliases, but you need to know when they're useful and when they aren't.

Remove excess spaces from in between two words with T-SQL

Here's a scenario you may have found yourself in: You allow your users to enter their names into a database application, and you've noticed that there are a lot of entries like this:

John    Smith
Jane    Doe
Billy   Carter
Don     King

With T-SQL's string manipulation capabilities, it isn't terribly difficult to strip out the whitespace between two words. The following snippet should get you thinking in the right direction. Try it out, and then see if you can adapt it to your needs:

DECLARE @Name VARCHAR(100)
DECLARE @intPosn INT

SET @Name = 'John         Doe'
SET @intPosn = CHARINDEX (' ' , @Name )

SELECT SUBSTRING(@Name,1,@intPosn-1) + ' ' +
LTRIM(SUBSTRING(@Name,@intPosn,LEN(@Name))) AS Name

Sort NULL values at the end of your T-SQL queries

In an ordered query, it's pretty obvious that NULL values should sort together, right? But there's nothing in the ANSI standards to determine whether NULL values should sort at the beginning of the order or at the end. In the absense of any clear definition, SQL Server arbitrary sorts NULL values at the beginning of the order. For instance, execute the following query:

   SELECT FirstName + ' ' + LastName, Region
   FROM Employees
   ORDER BY Region

and you'll observe that the employees with NULL Region values sort to the top of the resultset. But what if you want to sort NULL values to the end of the resultset? By using a CASE expression, you can do this easily. The following query demonstrates the technique:

   SELECT FirstName + ' ' + LastName, Region
   FROM Employees
   ORDER BY CASE WHEN Region IS NULL THEN 1 ELSE 0 
END, Region

Basically, we've included a computed column in the resultset. When the Region value is NULL, we return 1; otherwise, we return 0. In our ORDER BY clause, we sort first by this new column, then by Region. Even if non-NULL values sort before NULL values, 1 clearly does *not* sort before 0, so we achieve the results we were looking for.

Add Windows users to fixed server roles in SQL Server 2000

To add Windows users to fixed server roles, such as the sysadmin role, just follow these examples:

EXEC sp_addsrvrolemember "DOMAIN\Moe", "sysadmin"
EXEC sp_addsrvrolemember "DOMAIN\Larry", "serveradmin"
EXEC sp_addsrvrolemember "DOMAIN\Curly", "dbcreator"

This will allow you to log in to SQL Server using Windows integrated security while retaining the server roles your account should be able to access.



Internet Content Rating Association Valid XHTML 1.0! Valid CSS!

Alrak's Course Resources ©2002-2007 Karla Carter. All rights reserved. This material (including, but not limited to, Mini-lectures and Challenge Labs) may not be reproduced, displayed, modified or distributed without the express prior written permission of the copyright holder. For permission, contact Karla.