Post reply

Stored Procedure Boundaries

  • February 24, 2014 at 8:17 pm

    #282790

    Comments posted to this topic are about the item Stored Procedure Boundaries

    ___________________________
    Do Not Optimize for Exceptions!

  • February 24, 2014 at 9:24 pm

    #1691753

    Nice 'n Easy - thanks

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • February 24, 2014 at 10:19 pm

    #1691756

    Easy one...

  • February 24, 2014 at 10:25 pm

    #1691757

    Easy one, thanks.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • February 24, 2014 at 11:50 pm

    #1691765

    Good one, thank you for the post. 🙂

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • February 25, 2014 at 12:17 am

    #1691767

    Nice Question.

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • February 25, 2014 at 12:57 am

    #1691772

    GO is NOT a statement, but a batch separator. It is recognized only by SSMS and you can change it. E.g. you can change with RUN_BABY.

    This statement confirms it:

    execute ('

    SELECT 1 AS A

    GO')

  • February 25, 2014 at 1:05 am

    #1691773

  • February 25, 2014 at 1:30 am

    #1691776

    Hi Carlo,

    Thanks for your posts and reminders to batch separator settings.

    I referred to GO as a common and widely used batch separator in SQL Server world, not to it as a Transact SQL statement. I wanted to remind that everything in the batch staring with CREATE/ALTER PROCEDURE statement belong to the stored procedure regardless how the batch reaches its end.

    I had many situations that stored procedures perform some unexpected reads and I/O activities because someone forgot some SELECTs after the SPs BEGIN/END boundary. A common case is playing either with EXECUTE sp or some SELECT statements to verify if the SP works and then leaving the statements in the same batch with the assumption that everything out of BEGIN/END does not belong to the SP. And usually these statements don't break the SP contract and if they don't produce significant I/O it is not easy to identify them.

    Thanks.

    ___________________________
    Do Not Optimize for Exceptions!

  • February 25, 2014 at 3:36 am

    This was removed by the editor as SPAM

  • February 25, 2014 at 5:12 am

    #1691818

    milos.radivojevic (2/25/2014)

    Hi Carlo,

    I had many situations that stored procedures perform some unexpected reads and I/O activities because someone forgot some SELECTs after the SPs BEGIN/END boundary. A common case is playing either with EXECUTE sp or some SELECT statements to verify if the SP works and then leaving the statements in the same batch with the assumption that everything out of BEGIN/END does not belong to the SP. And usually these statements don't break the SP contract and if they don't produce significant I/O it is not easy to identify them.

    I once forgot IF EXISTS () DROP of next procedure from script. You can imagine it took some time to find out the cause of that other procedure's occasional mysterious disappearing.

    _______________________________________________
    www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore)

  • February 25, 2014 at 5:20 am

    #1691823

    Nice and simple question. Thanks.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • February 25, 2014 at 5:28 am

    #1691826

    Nice one!!!

  • February 25, 2014 at 5:28 am

    #1691827

    nice question. Thanks for sharing

  • February 25, 2014 at 6:22 am

    #1691844

    Hi Nenad,

    Yes, this can be painfull. Therefore is better to use the pattern IF NOT EXISTS - CREATE than IF EXISTS - DROP. In case of stored procedures it would be IF NOT EXISTS - EXEC ('CREATE...).

    Tnx.

    ___________________________
    Do Not Optimize for Exceptions!

Viewing 15 posts - 1 through 15 (of 33 total)

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