Every time I need to work with SQL, it never ceases to amaze. The language is simply such an abortion in terms of development enviroments and simple programmer usability. Naturally, alot of this stems from my inexperience with the language. Mostly this post is just my own personal gripes with about SQL.
So, at work I find that I need a function which takes a variable number of GUIDs, and presents me with a compounded status integer, pulling status indicators for each GUID, and producing a single status. Should be simple right?
First off, I need this to be a function, so I can use it in
SELECT constructs for a view. Why SQL has these random limitations on what can appear where, I’ll never know, but there it is. Then, the wild notion that I could pass in a variable number of GUIDs is killed fairly off quickly as SQL doesn’t support variadic functions.
So ok, I need to pass in the GUIDs in a single data structure. Of course, I know right off the bat, that I can’t pass tables (<sarcasm>why would anyone wanna do that anyway?</sarcasm>), so we’ll just pack a string with the GUIDs I need. I’ll live with
myFunction("guid1,guid2,guid3"). So taking a look over the extremely rich string function library in SQL, I notice there’s no split function. So I need to write that first.
Writing an SQL string split function
So let’s just lay it out there. In all it’s glory.
CREATE FUNCTION dbo.Split (@String VARCHAR(500), @Delimeter CHAR(1)) RETURNS @Strings TABLE (String VARCHAR(500)) AS BEGIN DECLARE @Position INT, @Next INT, @DelimeterWidth INT, @Substring VARCHAR(500) SET @Position = 1 SET @Next = 1 -- set to 1, just to get past initial while cond SET @DelimeterWidth = LEN(@Delimeter) WHILE @Next > 0 BEGIN SET @Next = CHARINDEX(@Delimeter, @String, @Position) IF (@Next-@Position) >= 1 BEGIN SET @Substring = SUBSTRING(@String, @Position+1, @Next - @Position - @DelimeterWidth) INSERT INTO @Strings(String) VALUES(@Substring) END SET @Position = @Next END RETURN END
You’ll notice this returns a table. This table is btw also the only one you have to work with, no creating temporary tables! For some reason, you’re not allowed to create tables inside functions. So once you’re past the 1-indexed strings, you’re almost there . I know that 0 versus 1 indexing is a religious war onto itself, but given Cantor’s ordinals start at 0, it does not seem entirely silly either. For a language so focused on sets, it seems a strange choice.
They phoned it in
SQL sometimes really feels like they sat down, thought about
SELECT, sub-selects, joins, and the related set algebra, and decided “nice work” and phoned the rest in. Even
INSERT makes no sense, why do we need to seperate the column names from the values?
INSERT INTO tbl(col1, col2, col3) VALUES(val1, val2, val3)
UPDATE does it right.
UPDATE tbl SET col1=val1, col2=val2, col3=val3
Know what’s being sold as a exciting new feature in SQL 2008? Declaring and assigning values in one line!. This is how low the bar is set. SQL is obviously not going anywhere, but I can’t for the life fathom why it has to be so abysmal at almost everything, except the little core which it does so well.