SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Building Better Stored Procedures


Building Better Stored Procedures

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (682K reputation)SSC Guru (682K reputation)SSC Guru (682K reputation)SSC Guru (682K reputation)SSC Guru (682K reputation)SSC Guru (682K reputation)SSC Guru (682K reputation)SSC Guru (682K reputation)

Group: Administrators
Points: 682164 Visits: 21588
Comments posted to this topic are about the item Building Better Stored Procedures

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
sqlservercentral-631096
sqlservercentral-631096
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 36
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.
Jo Pattyn
Jo Pattyn
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30486 Visits: 11158
Thanks for the article.
The code about create or alter , just before the nocount section, mentions "drop if exists" instead of "create or alter"
kevin.wallace
kevin.wallace
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 111
Excuse my ignorance. What is a VCS?
DinoRS
DinoRS
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1781 Visits: 823
VCS would be a Version Control System like TFS, Git and such.
jorgemor17
jorgemor17
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 162
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 Smile
dale_berta
dale_berta
SSC Eights!
SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)

Group: General Forum Members
Points: 903 Visits: 243
sqlservercentral-631096 - Thursday, March 14, 2019 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.

Nailed it! We do a CREATE with initial permissions. All subsequent changes are ALTERs, which almost never have permission changes.

Andy Warren
Andy Warren
SSC Guru
SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)

Group: Moderators
Points: 103730 Visits: 2943
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/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
RonKyle
RonKyle
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30033 Visits: 4649
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.




jonathan.crawford
jonathan.crawford
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5914 Visits: 1635
Andy Warren - Thursday, March 14, 2019 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/

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search