Post reply

Bad Stored Procedures

  • September 20, 2024 at 12:00 am

    #4451091

    Comments posted to this topic are about the item Bad Stored Procedures

  • September 20, 2024 at 9:59 am

    #4460756

    It's quite a minor SQL WTF in my opinion - probably doesn't significantly affect performance, just a bit slow and harder to plan. Security wise it would depend on whether the variables could ever be unsanitised. Awful to profile, look at, and clumsy of course.

    If you think that's bad I could give you some stories 😉

  • September 20, 2024 at 1:33 pm

    #4460843

    The fact that most input parameters are defined as NVARCHAR (4000) is evidence that the original code was generated by an ORM.

    My guess is that the developers were instructed to remove all embedded SQL from the application, so they fast tracked a project to port it all to stored procedures.

    This line of code in the WHERE clause explains why the SELECT statement is generated dynamically.

    PropertyName IN ( '+ @PropertyNameString + ')

    There are situations where reproducing the functionality of ORM in a stored procedure is difficult without some major refactoring. They could have avoided dynamic SQL by using the  STRING_SPLIT() function or by using a table valued parameter instead.

     

     

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • September 20, 2024 at 2:56 pm

    This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply