• King (unregistered)

    Newly started, boss immediately needs new features, and so the madness continues.

  • (nodebb)

    I started such a job 6 years ago. Worked my way up to the sole senior developer, and I refactor with fire and brimstone. We slowly start to see how the software is supposed to work, and usually throw way about 70% of the lines of code. Disclaimer: I work in an industrial automation context. It's so funny to replace swaths of barely functional code with one precise mathematical equation.

    Someday, I dare say, some manager may take notice. ( I won't hold my breath.)

  • L (unregistered)

    probably CHAR(39) means both "a 39-character string" and "the ASCII 39 a.k.a ['] " depending on how it's parsed, but don't quote me on it

    also this entire thing ticks me

  • pah (unregistered)

    DECLARE @TICK CHAR(1) defines it as a 1-character string. SET @TICK = CHAR(39) sets it to a single quote character.

  • Paul (unregistered)

    I've got an itching from the tick

  • Dr. BOM (unregistered)

    Actually, the @TICK is set to the single quote ('). The CHAR(1) is defining a fixed width string variable of length one and then SETting it to the single quote. I presume they're doing this because they don't like looking at the way you escape single-quotes in SQL by doubling them up, i.e. '', as it's very easy to confuse it with a double-quote (" vs '').

  • Kindadarii (unregistered)

    Erm... hate to burst your bubble, but char(39) is an apostrophe. They don't know how to properly escape it in the built string, it seems. Yeay overloaded keywords!

  • (nodebb)

    I assume that one of the comments currently held for moderation points out that ASCII character 39 is a single quote (').

  • Hans (unregistered) in reply to Domin Abbus

    Someday, I dare say, some manager may take notice. ( I won't hold my breath.)

    By throwing away 70% of the lines of code, the application is less big == less prestigious. And as the functionality is unchanged, you "didn't do anything". Most of all not his/her pet change-of-the-week. So hope the manager doesn't notice ;-)

  • Dr. BOM (unregistered)

    Adding another thought - I can almost see why they're using dynamic SQL instead of just a plain statement. The issue comes from the @PropertyNameString variable. Note that when it's used, it's not surrounded by a pair of @TICKs. This, plus the fact that it's used with an IN statement, suggests that the value of @PropertyNameString is a comma separated list of quote delimited values. i.e. something like 'value1', 'value2', 'value3'. So when the SQL is evaluated, it gets treated as e.g. three separate values.

    Depending on which engine this was written for and how old the code is, they may not have had access to a function that would do a string_split and treat the subvalues in @PropertyNameString as individual values. Ultimately, if I were rewriting this in SQL Server 2016 or later, I'd string_split @PropertyNameString, remove the opening and closing single-quotes on the values, and then TRIM any whitespace leftover.

  • (nodebb)

    There are sometimes cases where you want to use Dynamic SQL. This is not one of them, and probably does indicate someone copied the code from ASP or PHP into a stored proc for "performance" and didn't actually know how stored procs work.

  • (nodebb)

    It reminds me of working for a financial firm about 30 years ago that had strict restrictions on database changes. I once had to wait almost 2 months to have a column added to a table. We were also restricted to ONLY using stored procedures and considered writing a stored procedure that would let us do anything. We didn't.

  • Geoff Speare (unregistered)

    CHAR(39) is in this case ASCII character 39, the single quote. They are escaping them out because they couldn't figure out how to put them in the strings (different SQL engines have different methods for this). That the function is the same as the data type is just extra special.

  • (nodebb)

    Why do something simple and functional when you can get famous on The Daily WTF.

  • Don (unregistered)

    I don't really see a WTF. This seems like a straightforward ish way to handle an IN statement coming from a string variable I have always thought this is a big miss in SQL Server "perfectly well" variable handling

  • Conradus (unregistered)

    @SQLSelectII: Electric Boogaloo

  • Mike Sandbox (unregistered) in reply to Hans

    With 70% less code, if the managers notice it'll be because 'The program is so simple, why do we need to pay someone so much to maintain it?'

  • (nodebb)

    Sounds to me like a classic case of an incompetent developer thinking that some technology will save him from himself. I can almost see the conversation that happened (possibly inside a single developer's head)...

    Stored procedures can increase performance, increase security, allow code reuse, and make it easier to build multiple UIs that perform the same tasks. Let's use stored procedure for everything.

    Then the developer proceeds to use stored procedures, but in a way that accomplishes none of these things and makes the application worse.

  • Duke of New York (unregistered)

    This is the kind of code that is best viewed in low-res black-and-white video while a narrator says "There has to be a better way!"

  • Duke of New York (unregistered) in reply to Geoff Speare

    Which SQL DB does not escape quotes by doubling? Never mind that portability of SQL code isn't a practical thing.

    This is just another "developer" who will try every option except learning the tech.

  • ZZartin (unregistered)

    Is the only reason they did this so they can print out the statement?

  • (nodebb)

    @Jaime ref

    Then the developer proceeds to use stored procedures, but in a way that accomplishes none of these things and makes the application worse.

    IOW ... When the only tool you know how to use is a foot-gun, everything looks like a foot. BLAM BLAM BLAM

  • gggustafson (unregistered) in reply to Geoff Speare

    ASCII code 39 is the apostrophe not a quote which is ASCII code 34.

  • Registered (unregistered)

    Great timing - today is International Apostrophe Day!

  • Officer Johnny Holzkopf (unregistered) in reply to gggustafson

    That is correct - decimal 34 is " (double quote, inch, seconds), decimal 39 is ' (single quote, apostophe, minutes). This might work as long as you don't cross the boundary of ASCII, because in EBCDIC... well, nobody uses that anymore, right?

  • Loren Pechtel (unregistered)

    @TICK is a tick mark, aka an apostrophe. In this sort of situation it actually makes a certain amount of sense--when your escape character is your character you can very easily end up with some stuff that's pretty hard for a human to follow. I have been known to define constants to make things more readable even when a perfectly good escape exists.

  • Jmm (unregistered)

    It appears to me as if both your criticisms are incorrect. The @TICK thing has been explained by many, but noone has said anything about the @ENVIRONMENT claim, that the @TICKs are superfluous. The reason the @TICKs are necessary is because it is all in a string and the SQL parser has to parse the string. With @TICKs, the string looks like Environment = 'some value', without, it looks like Environment = some string. Which one do you think will throw a syntax error?

  • (nodebb) in reply to gggustafson

    ASCII 34 is a double quote. 39 is an apostrophe but was traditionally used as a single quote when ASCII was all you had. I don't know any programmers who don't call it a "single quote".

    Addendum 2024-08-19 08:58: In fact, on US keyboards it mirrored the back tick and so was sometimes legitimately used as a closing single quote (see the M4 macro language for example).

  • Joe (unregistered)

    Perhaps some confusion is caused by the fact that Office apps (for example) turn a single-quote into an apostrophe automatically for you. 39 is a single quote, not an apostrophe. If you type a word with an apostrophe into Word, it'll place a different character than if you type the same word into, say, Notepad, because Notepad isn't changing the single-quote into an apostrophe for you.

    By the way, same goes for double-quotes. Word will change your double-quotes into opening and closing double-quotes (or whatever they're called). The resulting characters in Word won't be 34, but they'll remain 34 in something simple like Notepad, and in every IDE on the planet.

    For kicks, these are the characters I copied out of Word:

    select 'apostrophe', ascii('’') union select 'opening single quote', ascii('‘') union select 'closing single quote', ascii('’') union select 'opening double-quote', ascii('“') union select 'closing double-quote', ascii('”')

    apostrophe 146 opening single quote 145 closing single quote 146 opening double-quote 147 closing double-quote 148

    (yes I manually re-ordered the output to match the SQL lines)

Leave a comment on “Stored Procedures are Better”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article