I recently learned of the concept of Spartan Programming and since then, I’ve been trying to find ways to apply some of the techniques.

As a talentless hack, some of the ideas are beyond me, but one of them — reducing the number of variables — I am capable of. I was working on a typical user-defined SQL function that was something like this…

CREATE FUNCTION do_something_function(@id AS INT)

RETURNS DATETIME

AS
BEGIN
   DECLARE @date DATETIME

   SELECT @date = date_field
     FROM some_table
    WHERE id = @id

   RETURN @date
END

I got me wondering if I could get rid of the return variable in simple functions that return a scalar variable like this. Well, it turns out you can. After working on several stored procedures that were written by someone obsessed with sub-queries, I thought I might be able to use one of his techniques in a way that was actually useful. This is what I did…

CREATE FUNCTION do_something_function(@id AS INT)

RETURNS DATETIME

AS
BEGIN
   RETURN (
      SELECT date_field
        FROM some_table
       WHERE id = @id
   )
END

I crossed my fingers and ran it and to my surprise it worked. That was a satisfying five minutes.

Have you ever needed to kill all the connections to a database in MS SQL Server? Do you go to the Process Info screen, right-click every process, clicking Kill Process and then hitting Y in the confirmation box? What a hassle.

(more…)

As part of a project at work, I found myself needing to update every field in an entire database that contained a certain value. If I had needed to do this in a database I had created or a database that didn’t have hundreds of tables, I might have done it manually like I have done it in the past. But that wasn’t the case, so I needed to find a better solution.
(more…)