Unstructured Query Language

lomac

Looking at some Stored Procedures for an internally developed app I've yet to have the misfortune of dealing with (but am currently getting ready to migrate my site to) I found something a bit like this*:

ALTER PROCEDURE sp_ADD_USER_ACCOUNT (@UserID VARCHAR(50), @Workstation VARCHAR(50)) AS
-- ...snip...
DO_NUMBER:

-- Generate unique account number
SET @AccountNum = (CAST(DATEPART(ss,GETDATE()) As Varchar(4))+ CAST(DATEPART(hh,GETDATE()) As Varchar(4))+ CAST(DATEPART(dd,GETDATE()) As Varchar(4))+ CAST(DATEPART(yy,GETDATE()) As Varchar(4))+ CAST(DATEPART(mi,GETDATE()) As Varchar(4))+ CAST(DATEPART(mm,GETDATE()) As Varchar(4)))

WHILE (LEN(@AccountNum) < 18)

BEGIN

SET @AccountNum = @AccountNum + '1'

END
Set @Exists=(SELECT AccountNum FROM tblAcc WHERE AccountNum = @AccountNum)

If @Exists IS NOT NULL

BEGIN

GOTO DO_NUMBER

END
-- ...snip...
INSERT INTO tblAcc (AccountNum, ...etc etc)

It's not transactionalised, nor does it try to get an exclusive lock on the table. It functions fine with a single client connected, but sometimes (quite often) it will be called by a few clients in very quick succession. There's no unique constraint on that column (although it being non-unique has some pretty bad side-effects).

More WTFs: the other day someone noticed another procedure called something like sp_MODIFY_ACCOUNT (supposed to modify a single account) included this:

UPDATE tblUserInfo SET ...(every field in table = every passed value from form)... WHERE UserID LIKE '%' & @UserID

(The UserID above is not the same as the account number in the first example, it's a number usually <10000). Changing user number 3052's surname made about 2000 rows of data equal. When asked what they were thinking, the creator of this fine work of art commented "yes, but the % is ignored, because it's doing a full text match".

I've almost convinced them transactions are a good thing, but unique constraints "can cause errors in the software", so I'm going to ride that one out I guess...

*Anonymized, not tested, no doubt a few mistakes in SQL syntax.

valerion

Ah.... so many WTFs.... I love the unique account number generation.

But most of all I love the GOTO. I've been using SQL for years. I deal with databases that handle tens of millions of transactions daily. And I never even knew you could use a GOTO.

lomac

o.O

DBA: "thanks anyway. you are doing great testing our system, they need lots of testing as most of them came out without proper testing or at least enough time to test."

Edit: Also, I don't think I made it quite clear in the OP. Sometimes, in the time between when it checks whether an account number is available and when it actually stores it, another client will have created an account with the same number. I'm not sure if they assumed it was transactionalized, or whether they just assumed it wouldn't be called often enough for it to be a problem.....

jasmine2501

@lomac said:

but unique constraints "can cause errors in the software"

OMG - I have heard that so many times. It drives me nuts. Constraints exist to prevent malicious clients from screwing up the data. Why don't people understand that?