SQL Parameters? Don't need them.



  • I continue to be amazed by this code base. I found a function called SqlQuote in the code base, which just exactly what you would think it does. I then decided my task for today will be to delete that function and then fix everything that breaks.

    Some rewriting to use parameters, and refactoring later (They had the same block of code that loads data from a SqlDataReader into a List in 6 functions, that is now one function that does that), I have encountered this beast:

    _SQL = string.Format(@"
                    DECLARE @temp1 int
                    SET @temp1 = -1
                    DECLARE @temp2 int
                    EXEC [dbo].[upPickListValueInsert]
    	                @ListCode = {0},
    	                @ListDescription = {1},
    	                @PickListReferenceValueIDY = NULL,
    	                @PickListName = NULL,
    	                @IsSystem = NULL,
    	                @CreatedbyUser = {2},
    	                @UpdatedbyUser = NULL,
    	                @CommunityIDY = {3},
    	                @Inactive = NULL,
    	                @DisplayOrder = NULL,
    	                @PickListTypeIDY = {4},
    	                @PickListValueIDY = @temp1 output,
    	                @Return = @temp2 output
                    SELECT @temp1
                ", new object[] { SQLMethods.ToSQL(_ListCode), SQLMethods.ToSQL(_ListDescription), _UserIDY, 
                     SQLMethods.ToSQL(_CommunityIDY), _PickListTypeIDY });
    

    The SqlMethods.ToSQL crap at the bottom (And I love the consistent capitalization there...) have also been deemed that they should not exist, as most of the time they just called the SqlQuote function, or mapped a bool to 1 or 0, things done quite well by the runtime when using Sql Parameters.

    But, my thoughts when finally figuring out what exactly this block was: What is this, I don't even

    I should also note a "Pick List" in this program is actually a lookup table. It still confuses me, since I've done software for warehouses before, where a pick list is a very different thing



  • @bardofspoons42 said:

    I found a function called SqlQuote in the code base, which just exactly what you would think it does.

    I have absolutely no idea what a function named SqlQuote would do.



  • This keeps getting better. i was looking into all the NULLs passed as parameters, seeing if they used any default values for the parameters in SQL, instead, I find this

    	IF @IsSystem IS NULL
    	BEGIN
    		SET @IsSystem = 0
    	END
    
    	IF @Inactive IS NULL
    	BEGIN
    		SET @Inactive = 0
    	END
    
    	IF @DisplayOrder IS NULL
    	BEGIN
    		SET @DisplayOrder = 1
    	END
    

    This is going to be a long week...



  • @blakeyrat said:

    @bardofspoons42 said:
    I found a function called SqlQuote in the code base, which just exactly what you would think it does.

    I have absolutely no idea what a function named SqlQuote would do.

    Presumably it puts single-quotes around SQL strings.

    @bardofspoons42 said:

    This keeps getting better. i was looking into all the NULLs passed as parameters, seeing if they used any default values for the parameters in SQL, instead, I find this

    	IF @IsSystem IS NULL
    	BEGIN
    		SET @IsSystem = 0
    	END
    
    	IF @Inactive IS NULL
    	BEGIN
    		SET @Inactive = 0
    	END
    
    	IF @DisplayOrder IS NULL
    	BEGIN
    		SET @DisplayOrder = 1
    	END
    

    This is going to be a long week...

    Because select @Variable = isnull(@Variable, defaultValue) is too complex!


  • Trolleybus Mechanic

    @blakeyrat said:

    @bardofspoons42 said:
    I found a function called SqlQuote in the code base, which just exactly what you would think it does.

    I have absolutely no idea what a function named SqlQuote would do.

     

    It makes a webcall to Stack Overflow, looking for information defending the use of SQL. It then wraps it in a quote supposedly attributed to an industry leader that a CEO would recognize, like Steve Jobs or Enstien. Then when the CEO comes down talking about this amazing new technology called NoSQL, you have a bunch of bite-sized, easily digestible quotes to distract the CEO with.

    Version 2 will take the quotes, and render them in a soothing font against an inspirational background image like a sunset, or a runner breaking free of The Box.



  • @The_Assimilator said:

    Because select @Variable = isnull(@Variable, defaultValue) is too complex!

    Or just defining a default value for that parameter in the stored proc definition

    My new favorite use of that SqlQuote function I deleted? It was used when they were building up XML by string concatenation. I want to find the people that wrote this and use some thumbscrews on them...



  • @blakeyrat said:

    I have absolutely no idea what a function named SqlQuote would do.
     

    And that's exactly what it does.

    It's like Schrödinger's Function -- You put a string into a box, wait a while and then it will either have escapes before any quotation marks, or it will be dead.  But you'll never know which until you run it.

     



  • @Lorne Kates said:

    It makes a webcall to Stack Overflow, looking for information defending the use of SQL. It then wraps it in a quote supposedly attributed to an industry leader that a CEO would recognize, like Steve Jobs or Enstien. Then when the CEO comes down talking about this amazing new technology called NoSQL, you have a bunch of bite-sized, easily digestible quotes to distract the CEO with.

    Oh I was thinking it phoned up Microsoft, IBM, and Oracle with a list of server configurations and estimated storage use and collected quotes on SQL Server licensing costs.



  • @blakeyrat said:

    @Lorne Kates said:
    It makes a webcall to Stack Overflow, looking for information defending the use of SQL. It then wraps it in a quote supposedly attributed to an industry leader that a CEO would recognize, like Steve Jobs or Enstien. Then when the CEO comes down talking about this amazing new technology called NoSQL, you have a bunch of bite-sized, easily digestible quotes to distract the CEO with.

    Oh I was thinking it phoned up Microsoft, IBM, and Oracle with a list of server configurations and estimated storage use and collected quotes on SQL Server licensing costs.

    No, it's just a linear-time sort function. They named it after a horrid anti-pattern so foreign spies won't catch on to it.



  • @bardofspoons42 said:

    @The_Assimilator said:
    Because select @Variable = isnull(@Variable, defaultValue) is too complex!
    Or just defining a default value for that parameter in the stored proc definition
    We have an app with a similar approach to calling stored procedures that renders your sane suggestion impossible.  It interrogates the system catalog for the parameters of the sp (on every call, no caching) and expects procedures to be called as:
    int ExecuteStoredProcedure(string ProcName, object[]] ParameterValues)

    So, if a new parameter is added to a stored procedure, every caller now has to add an element to the parameter array or the call will fail with an "index outside bounds of array" exception.  The fact that a parameter has a default value doesn't prevent their brilliant data access code from requiring the number of elements in the array to match the number of parameters in the procedure.



  • @blakeyrat said:

    @bardofspoons42 said:
    I found a function called SqlQuote in the code base, which just exactly what you would think it does.

    I have absolutely no idea what a function named SqlQuote would do.


    returns "Sql", including the quotes, obviously



  • @blakeyrat said:

    @bardofspoons42 said:
    I found a function called SqlQuote in the code base, which just exactly what you would think it does.

    I have absolutely no idea what a function named SqlQuote would do.

     

    Clearly, it constructs an object. It's derived from PrintableCharacter which is derived from Character, and the current platform derives the actual character it wants to use for SQL quotes from SqlQuote. Then the quoting implementation doesn't need to have knowledge of the underlying character set, and can just dynamic_cast the character to SqlQuote and if it succeeds it knows it's a quote character. Thus when you don't know what characters SQL uses for quoting you don't have to worry, you can just create a new SqlQuote and use it directly.



  • @SEMI-HYBRID code said:

    @blakeyrat said:
    @bardofspoons42 said:
    I found a function called SqlQuote in the code base, which just exactly what you would think it does.

    I have absolutely no idea what a function named SqlQuote would do.


    returns "Sql", including the quotes, obviously

    No, you're thinking of the function SqlDoubleQuotes. SqlQuote returns...
    'Sql

    ...so it can be imported into Excel as text instead of a being converted to a numeric value.


Log in to reply