February 24, 2014 at 8:17 pm
Comments posted to this topic are about the item Stored Procedure Boundaries
___________________________
Do Not Optimize for Exceptions!
February 24, 2014 at 9:24 pm
Nice 'n Easy - thanks
Hope this helps...
Ford Fairlane
Rock and Roll Detective
February 24, 2014 at 10:19 pm
Easy one...
February 24, 2014 at 10:25 pm
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
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
Nice Question.
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
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
February 25, 2014 at 1:30 am
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
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
Nice and simple question. Thanks.
February 25, 2014 at 5:28 am
Nice one!!!
February 25, 2014 at 5:28 am
nice question. Thanks for sharing
February 25, 2014 at 6:22 am
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