Defining Stored Procedures

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715401

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

  • Lynn Pettis

    SSC Guru

    Points: 442138

    An easy one.

  • HappyGeek

    SSCoach

    Points: 18662

    Good question, nice reminder, thanks Steve.

    ...

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71362

    Nice question, thanks Steve

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    β€œlibera tute vulgaris ex”

  • Sean Lange

    SSC Guru

    Points: 286422

    Nice easy question to start the week with. Reminds of a developer we had a few years ago. He pushed his first app to production and everything was broken. Kept getting error message that procedure X didn't exist. Every single thing in this app worked once and then crashed. Really had me shaking my head. Then started looking at the store procs that were created. Every one of them had a "if exists(....) drop procedure" at the end of them. When asked about why that was there I was told that they thought the begin/end would be sufficient. I was laughing so hard I nearly died. The code was literally eating itself as each time one procedure ran a different one was getting dropped. The big deployment script needed to have GO added in about a hundred places and redeploy the whole thing. πŸ˜€

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Luis Cazares

    SSC Guru

    Points: 183545

    Sean Lange - Monday, March 4, 2019 8:11 AM

    Nice easy question to start the week with. Reminds of a developer we had a few years ago. He pushed his first app to production and everything was broken. Kept getting error message that procedure X didn't exist. Every single thing in this app worked once and then crashed. Really had me shaking my head. Then started looking at the store procs that were created. Every one of them had a "if exists(....) drop procedure" at the end of them. When asked about why that was there I was told that they thought the begin/end would be sufficient. I was laughing so hard I nearly died. The code was literally eating itself as each time one procedure ran a different one was getting dropped. The big deployment script needed to have GO added in about a hundred places and redeploy the whole thing. πŸ˜€

    And that's one of the reasons that I don't like to use BEGIN...END to encapsulate procedures. Many of us have made a similar mistake and included lines of code that shouldn't be part of the procedure (although, this is the worst story I've seen).

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Sean Lange

    SSC Guru

    Points: 286422

    Luis Cazares - Monday, March 4, 2019 9:29 AM

    Sean Lange - Monday, March 4, 2019 8:11 AM

    Nice easy question to start the week with. Reminds of a developer we had a few years ago. He pushed his first app to production and everything was broken. Kept getting error message that procedure X didn't exist. Every single thing in this app worked once and then crashed. Really had me shaking my head. Then started looking at the store procs that were created. Every one of them had a "if exists(....) drop procedure" at the end of them. When asked about why that was there I was told that they thought the begin/end would be sufficient. I was laughing so hard I nearly died. The code was literally eating itself as each time one procedure ran a different one was getting dropped. The big deployment script needed to have GO added in about a hundred places and redeploy the whole thing. πŸ˜€

    And that's one of the reasons that I don't like to use BEGIN...END to encapsulate procedures. Many of us have made a similar mistake and included lines of code that shouldn't be part of the procedure (although, this is the worst story I've seen).

    I am not a big fan of begin end either. It seems it just causes an extra indentation with no benefit.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Erin Dempster

    Newbie

    Points: 6

    Sean Lange - Monday, March 4, 2019 8:11 AM

    Nice easy question to start the week with. Reminds of a developer we had a few years ago. He pushed his first app to production and everything was broken. Kept getting error message that procedure X didn't exist. Every single thing in this app worked once and then crashed. Really had me shaking my head. Then started looking at the store procs that were created. Every one of them had a "if exists(....) drop procedure" at the end of them. When asked about why that was there I was told that they thought the begin/end would be sufficient. I was laughing so hard I nearly died. The code was literally eating itself as each time one procedure ran a different one was getting dropped. The big deployment script needed to have GO added in about a hundred places and redeploy the whole thing. πŸ˜€

    A number of years ago, I noticed stored procedures were running really slow, especially under a little bit of load (2 or 3 simultaneous connections).  After inspecting the stored procedures in test, we had defined explicit permissions on the stored procs, and we typically add the permissions when we created the proc.  Of course, the permissions were after the END, but there was no GO, so permissions were be re-added every time the proc ran.  Clearing those out sped everything up...imagine that :hehe:

Viewing 8 posts - 1 through 8 (of 8 total)

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