Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

Passing Arrays of Values as Parameters to Stored Procedures

I see many people have the need to pass in multiple pieces of information to stored procedures in a parameter, often as a CSV string. For example, you might have a stored procedure that will return customer info for all customer ID's passed in a CSV string, called something like this: Read more →

Using SQL to solve the "Sliding Tiles" puzzle

I just wanted to take a minute to highlight this thread in the sqlteam forums: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=39736 It's really interesting stuff, and a bunch of us worked together to come up with some really cool ideas about using T-SQL to solve the classic sliding tile puzzle -- you know, where tiles on a square board are mixed up, with one missing, and you slide them around until the picture is complete or they are in the proper order. Read more →

Keep those SQL Crosstabs flexible!

I recently helped someone with performing some simple cross tabs (or pivots) in SQL, such as: Select CustomerID, SUM(CASE WHEN type = 'Land' THEN Amount ELSE 0 END) as Land, SUM(CASE WHEN type = 'Sea' THEN Amount ELSE 0 END) as Sea, SUM(CASE WHEN type = 'Air' THEN Amount ELSE 0 END) as Air FROM SomeTable GROUP BY CustomerID Read more →