Stored Procedure Boundaries

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

    ___________________________
    Do Not Optimize for Exceptions!

  • Nice 'n Easy - thanks

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • Easy one...

  • Easy one, thanks.

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

  • 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.

  • 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/

  • 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')

  • 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!

  • This was removed by the editor as SPAM

  • 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)

  • Nice one!!!

  • nice question. Thanks for sharing

  • 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