Development

Sometimes you need dynamic SQL. Sometimes you don't know at run time where you're querying or what fields you need to bring back, or what your criteria will be, so there will always be a place for the use of dynamic SQL. You have to be careful how you use it, but if you do use it correctly it can be a powerful tool that you have in your tool belt. So, today I am going to show you a nice little general purpose procedure that you can write to pass in a few object strings and execute dynamic...

As a developer, I always enjoy new TSQL language features that can save me some time and potentially effort in accomplishing the same task that I otherwise could in a different fashion. These features aren't really a big deal to me to have but are sometimes fun. SQL Server 2008 has produced a couple of these features so I thought it might be worthwhile to take a look at them. Compound Operators This is a feature that exists in most common-day programming languages. The idea is simple; why use...

Ever had a column which had a pile of information delimited by commas that you want to unpack ? Well, it is not that hard so long as the information is pretty short, and tightly controlled... -- step 1 create a sample table used as a datasource... if object_id('tempdb..#tmp_colors','U') is not null drop table #tmp_colors create table #tmp_colors (Date datetime, Name varchar(60), Choices varchar(100)) insert #tmp_colors select '01 Mar 2009','John S','Red,Blue,Yellow'...

New to SQL Server 2005 (and present in SQL Server 2008) is the ability to use TRY..CATCH constructs inside of your TSQL code to catch error statements. If you've done any type of programming in other programming languages, you know that this is a very handy tool to have. Whats even better in my opinion is the ability to write a generic error handling procedure you can use inside of your CATCH blocks to capture and record errors when they occur. So, lets take a look at how we can take advantage...

Many times there is a need to strip the time off of a DATETIME value so you are left with the date only. I've seen a few different ways to handle this. To clarify, when I say a DATETIME value with no time, I really mean midnight (00:00:00.000) on that date. Converting to VARCHAR One way to remove the time from a DATETIME value is to convert it to a VARCHAR data type. This is handy when you need the date returned as a VARCHAR value and when you need the option to return the date in any number...

If you've looked up a Common Table Expressions in Books Online, you might have noticed that Microsoft says that you cannot have nested CTEs (meaning, you can't define a CTE in the definition of another CTE. Although this is technically true, you can still accomplish the same functionality in a different manner. Here is an example of a simple CTE using the AdventureWorks database that will return a list of orders. USE AdventureWorks ;WITH CTE_Orders (OrderID, OrderDate) AS ( SELECT PurchaseOrderID...