March 13, 2019 at 10:10 pm
Comments posted to this topic are about the item Building Better Stored Procedures
March 14, 2019 at 2:50 am
Hey Steve,
Great article, and thanks for sharing.
You discussed whether to use create procedure or create a stubb proc and do an alter. Finishing off with "I'm not sure which I like better, but I don't really think any of them are great".
Now, I'm no DBA so I may be talking hokum ... but as I understand it if you use "if exists drop proc" variation you lose any permissions allocated to the proc. The second version with the alter doesn't lose the permissions.
Now ideally we'd have permissions scripted as part of an upgrade procedure, but in real life permissions get added outside a normal release cycle.
Hence I tend to prefer creating a stubb and using alter, which may sway your indecision as to which is preferred 😉
Hope this helps.
March 14, 2019 at 3:02 am
Thanks for the article.
The code about create or alter , just before the nocount section, mentions "drop if exists" instead of "create or alter"
March 14, 2019 at 3:20 am
Excuse my ignorance. What is a VCS?
March 14, 2019 at 4:23 am
VCS would be a Version Control System like TFS, Git and such.
March 14, 2019 at 4:46 am
Thanks for sharing, Steve.
II do see a point about the ALTER statement, especially if you compare T-SQL to compiled languages. It's a good way to avoid that you inadvertently replace an exiting sp. I'll explain: when you have a, say, C# solution, if you try to write two different functions with the same name (and signature), you will get a compilation error. However, in T-SQL, we don't compile all the code, we just run scripts that do not have to have all the solution code. Thus, if CREATE could alter an existing sp, you could run a script that would replace an existing sp, when you actually wanted to create a new one, but you just had a typo in the name.
I hope I made myself clear 🙂
March 14, 2019 at 6:08 am
sqlservercentral-631096 - Thursday, March 14, 2019 2:50 AMHey Steve,
Great article, and thanks for sharing.
You discussed whether to use create procedure or create a stubb proc and do an alter. Finishing off with "I'm not sure which I like better, but I don't really think any of them are great".
Now, I'm no DBA so I may be talking hokum ... but as I understand it if you use "if exists drop proc" variation you lose any permissions allocated to the proc. The second version with the alter doesn't lose the permissions.
Now ideally we'd have permissions scripted as part of an upgrade procedure, but in real life permissions get added outside a normal release cycle.
Hence I tend to prefer creating a stubb and using alter, which may sway your indecision as to which is preferred 😉
Hope this helps.
Nailed it! We do a CREATE with initial permissions. All subsequent changes are ALTERs, which almost never have permission changes.
March 14, 2019 at 6:49 am
The begin/end optional (usually) syntax makes me crazy. I only use it when required, it just adds noise. Also a plug for set xact_abort on as a default in procs, too many people don't understand the behavior of transactions and errors/
March 14, 2019 at 6:58 am
The begin/end optional (usually) syntax makes me crazy. I only use it when required, it just adds noise.
Agreed. However, same for the NO COUNT ON. I don't use it unless there would be a demonstrated need for it. There are many times the rowcount has been necessary. Using this command just limits options.
March 14, 2019 at 7:20 am
Andy Warren - Thursday, March 14, 2019 6:49 AMThe begin/end optional (usually) syntax makes me crazy. I only use it when required, it just adds noise. Also a plug for set xact_abort on as a default in procs, too many people don't understand the behavior of transactions and errors/
I think it's useful, because you might create a procedure with 15 parameters, and there's that group of definitions and default values you have to read through before you start the actual procedure code. (not to mention any header comments, USE statements up front, settings you're modifying, etc.). Gives you a clear start/end for where the actual work is being done.
I'm assuming you're also only talking about the starting BEGIN and ending END around the whole procedure. Using BEGIN/END within a procedure to delineate sections of the code is VERY useful IMHO, especially because when connected, I can expand/collapse those BEGIN/END sections at will and only stare at the code I'm trying to fix, not slog through the whole thing top to bottom.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
March 14, 2019 at 7:24 am
I'm assuming you're also only talking about the starting BEGIN and ending END around the whole procedure.
Yes. I certainly use the combination as part of portions of a stored procedure.
March 14, 2019 at 7:49 am
jonathan.crawford - Thursday, March 14, 2019 7:20 AMAndy Warren - Thursday, March 14, 2019 6:49 AMThe begin/end optional (usually) syntax makes me crazy. I only use it when required, it just adds noise. Also a plug for set xact_abort on as a default in procs, too many people don't understand the behavior of transactions and errors/I think it's useful, because you might create a procedure with 15 parameters, and there's that group of definitions and default values you have to read through before you start the actual procedure code. (not to mention any header comments, USE statements up front, settings you're modifying, etc.). Gives you a clear start/end for where the actual work is being done.
I'm assuming you're also only talking about the starting BEGIN and ending END around the whole procedure. Using BEGIN/END within a procedure to delineate sections of the code is VERY useful IMHO, especially because when connected, I can expand/collapse those BEGIN/END sections at will and only stare at the code I'm trying to fix, not slog through the whole thing top to bottom.
I use comments to describe sections and so I don't use Begin/End to identify the body of the proc. Also, way back in 7 and 2000, I did some testing with procs that are called on a rapid fire basis and found that the use ob Begin/End actually slowed them down a bit. I know... sounds crazy but that was the only thing I changed in the tests. I haven't done the test since then but this thread reminds me that I've put it off long enough.
I also find Begin/End are a royal pain in the patooti when doing partial runs of existing code for troubleshooting purposes. Since I find no other value in Begin/End other than to delimit the body of the proc, I (and I don't use the word often) never use Begin/End to mark the body of the proc. I also taught the folks that I work with to avoid it for the same reason.
Now.... that's just my opinion on why I don't use it personally and try to enforce that in the shop I work in. If I were working in a shop where I wasn't the one that was large-and-in-charge or if there's some other utility that can be gain by the use of Begin/End proc body delimiters (perhaps to support some form of automation), then I'd happily use Begin/End. And, if someone takes great comfort in using it, I'd only explain to them once about the minor problems during urgent troubleshooting and then not bug them again.
Of course, if it does turn out that Begin/End does slow down rapid fire procs like I observed almost 2 decades ago, then my stance on not using Begin/End will become a lot more firm. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2019 at 8:12 am
@steve-2 Jones...
Real nice article, Steve, especially on the importance of properly commenting code and the inference that it should also be properly structured (first letter of SQL, right? :D) My rule-of-thumb for comments is that if you remove all the code, you should be able to build a functional flowchart from the comments. There's nothing worse that being given a 1200 line stored procedure to troubleshoot with no or inadequate comments (e.g. "Update the Customer Table" as a comment is totally inadequate).
I'll also stress the importance of the "flower box" header. We actually keep a revision history of changes in the header. If nothing else, it tells you who the previous SME's are so that you can hunt them down and pick their brain if you need to. We also have a section in the header called "Usage Examples" where there's a lick of code that the person doing troubleshooting can use/modify to run the stored procedure for something that is known to work instead of trying to figure out what all the possible values should be for a 15 parameter bit of code.
Last but not least, you can have a ton of external documentation for the code but, when all else fails and all that documentation is lost (and frequently is unless you have the "documentation religion", which most shops don't), the documentation in the form of well written comments is sometimes all there is and is usually better than frequently unmaintained external documentation.
I also agree that any code that can execute the statements should always have SET NOCOUNT ON and SET XACT_ABORT ON. They're virtually free and if you get into the habit (or template) of always using them unless they would do something contrary in the code (and should still be included even then but commented out with a comment as to WHY they must not be used).
Last must not least, you might want to avoid the use of @@IDENTITY in favor of the SCOPE_IDENTITY() function.. 😀 We have that in our coding standards.
Again, nice high-level article on some of the important things about code in SQL Server. Thanks for taking the time to write it.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2019 at 8:29 am
sqlservercentral-631096 - Thursday, March 14, 2019 2:50 AMHey Steve,
Great article, and thanks for sharing.
You discussed whether to use create procedure or create a stubb proc and do an alter. Finishing off with "I'm not sure which I like better, but I don't really think any of them are great".
Now, I'm no DBA so I may be talking hokum ... but as I understand it if you use "if exists drop proc" variation you lose any permissions allocated to the proc. The second version with the alter doesn't lose the permissions.
Now ideally we'd have permissions scripted as part of an upgrade procedure, but in real life permissions get added outside a normal release cycle.
Hence I tend to prefer creating a stubb and using alter, which may sway your indecision as to which is preferred 😉
Hope this helps.
I've tended to keep permissions with procs, so this hasn't been a big deal, but really I do the if exists() thing most of the time in the past. These days, create or alter is my go to coding style.
March 14, 2019 at 8:30 am
Andy Warren - Thursday, March 14, 2019 6:49 AMThe begin/end optional (usually) syntax makes me crazy. I only use it when required, it just adds noise. Also a plug for set xact_abort on as a default in procs, too many people don't understand the behavior of transactions and errors/
Good point on xact_abort. Should mention that.
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply