Creating Your First Stored Procedure

Question

Creating Your First Stored Procedure

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
February 2, 2019 at 1:22 pm

#77713

Comments posted to this topic are about the item Creating Your First Stored Procedure

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

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

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

Great article, Steve.

My only quandary is where does the idea of it being preferable to include BEGIN/END come from? Oracle? Portability? To be sure, I never include BEGIN/END in stored procedures mostly because it makes it more difficult to execute a part of the code during troubleshooting under the gun. You have to remember to comment out the BEGIN and then remember to uncomment it when you're done. It doesn't really bring anything to the game unless you know for sure that your code will be migrated to an RDBMS that requires it.

I also realize that it's a "Beginner's" article and so it's not designed to be all encompassing. I was happy to see the beginning of a "Flower Box" but both a more complete one and emphasizing that comments make code so much easier to troubleshoot would be good for beginners to understand.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

Jeff Moden - Monday, February 4, 2019 6:00 AM
Great article, Steve.
My only quandary is where does the idea of it being preferable to include BEGIN/END come from? Oracle? Portability? To be sure, I never include BEGIN/END in stored procedures mostly because it makes it more difficult to execute a part of the code during troubleshooting under the gun. You have to remember to comment out the BEGIN and then remember to uncomment it when you're done. It doesn't really bring anything to the game unless you know for sure that your code will be migrated to an RDBMS that requires it.
I also realize that it's a "Beginner's" article and so it's not designed to be all encompassing. I was happy to see the beginning of a "Flower Box" but both a more complete one and emphasizing that comments make code so much easier to troubleshoot would be good for beginners to understand.

I totally agree. I've also seen beginners (sometimes with multiple years of "experience") wondering why there's some unexpected behavior with the procedure. The reason was that they thought that the code they wrote after the END wasn't going to be part of the procedure. This usually happens with a variable used in a SELECT for debugging or permissions granted in inappropriate environments.

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

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 3

Re: Begin/end, I think this is a good programming practice and it shows that you intentionally put certain code in the procedure. If you're executing the code under the gun, I assume you're changing the parameters to a DECLARE? I would note that's likely a more advanced concept.

The "basics" is just that. I feel that sometimes we try to write all encompassing articles that become too long and confusing for someone that wants to get started. I'd like to see more developers start using these, and my view is that the ought to try and write some based on a quick introduction.

I may try to write something more advanced than this, but it's a question of time and resources. Someone else is welcome to do so if they like.