My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.

SQL

Always remember that the column names returned in a result set do not have to be the same as what you eventually output at your presentation layer.

For example, suppose you have stored procedure that accepts a @CurrentYear parameter and returns a sales variance between the current year and the previous year for each customer. I often see programmers struggling with writing dynamic SQL to produce output like this:

Notice that with that set of columns, no dynamic SQL is needed, and the column names returned are always constant regardless of the value of the @CurrYear parameter.

As mentioned, the fact that your data set has columns labelled "CurrentYear" and "PrevYear" does not mean that you cannot re-label them any way that you like on your report or web page.

If your client code called the stored procedure and provided a @CurrentYear parameter, then it knows exactly what "CurrentYear" and "PrevYear" represent, and you can easily label the columns in the final result exactly as needed with simple formulas or a few lines of code.

Remember that in the world of relational database programming, table names and column names should be constant -- only the data itself should change. Focus on returning consistently structured data from your database, and let your client applications handle the labeling of columns to make them look nice.