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


Building Better Stored Procedures


Building Better Stored Procedures

Author
Message
andycao
andycao
Say Hey Kid
Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)

Group: General Forum Members
Points: 665 Visits: 1081
dale_berta - Thursday, March 14, 2019 6:08 AM
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.

No one else has mentioned this: ALTER also lets you keep the CreatedOn date of the SP. That information can be golden! It's almost like folks here have never worked on systems that are many, many years old.




David Rueter
David Rueter
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2527 Visits: 638
Regarding CREATE vs. ALTER...maybe it is just personal taste, but I don't mind--or maybe even like--the two different commands. We are not just declaring a procedure (or a function, or a view, or whatever), but we are explicitly creating an object in the database. Once there is an object in the database, it should not change unless we explicitly want it to change.

For the same reason that I am glad SQL gives us separate INSERT and UPDATE statements for rows, I'm glad it gives us CREATE and ALTER for objects.

Yes, sometimes an 'upsert' is handy--and there are ways to do that if needed. But since dropping and re-creating objects is not without side effects (such as loosing permissions, loosing asymmetric keys, executing DDL triggers, triggering a re-compile of the object, changing the object id, etc.) it is possibly dangerous to be in the mindset that a DROP + CREATE produces the same results as an ALTER.
David Rueter
David Rueter
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2527 Visits: 638
This might be a good time for a plug for my SQLVer Version Tracking article. (It might be time for me to write a new article on this that describes some updated capabilities). One new thing this lets you do is have a comment block like:


/*/ver Here is a version-specific comment.

This will be saved to sqlver.tblSchemaLog, but will be stripped from the source code of the object.
*/


This way you can simply execute:
ver 'MyProcName'


(ver being a synonym to a SQLVer stored procedure)

This will return a reultset of all versions of this object, including date/time/user of the update, the version-specific comments, the actual SQL command executed, and the hash of the cleaned source code.

Or, you can use a comment like this to update the object-level (i.e. non-version-specific) comments for the object in sqvler.tblSchemaManifest:


/*/manifest Here is a comment for the object itself.

This will be saved to sqlver.tblSchemaManifest (replacing any existing comment there), but will be stripped from the source code of the object.
*/

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: 682132 Visits: 21588
David Rueter - Thursday, March 14, 2019 9:59 AM
If you create comments before the BEGIN statement, the comments show up in the SQL Profiler command text (and other places you look at command text). This is very annoying when trying to do analysis or performance tuning, because you see the silly comment instead of the actual command being executed. I'd strongly suggest moving the comment to after the BEGIN so as not to cause frustrations down the road.


This doesn't seem to be the case in the XEvents Profiler. I'd strongly suggest moving to XE for future work, though if you are pre-2012, that's not necessary.


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
SimonHolzman
SimonHolzman
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 71
I like to practice "defensive" programming and so I want the system to fail if I accidentally hit "execute".

Just having CREATE prevents this accidentally replacing the original version unless I deliberately change it to say ALTER.

Similarly, the comments should only be in the body of the Stored Procedure so that, if the original source file gets lost, they are included in the copy of the Stored Procedure that is actually running (a good reason not to encrypt the SPs).

In my experience, programmers are much more likely to update comments that are embedded in the code than to update notes that are in a separate file and this even applies to the original source code file. Encrypting the SP can force the programmer to use the source code file but there is still the risk that the source code file will get lost or might not be the same version as the one actively running. Thus, I update the live code and keep backups of the before and after versions.

This is not the way it "should" be done, but it is more reliable in a small environment where there is no-one with the time and authority to enforce proper standards.
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
I like to practice "defensive" programming and so I want the system to fail if I accidentally hit "execute".
Just having CREATE prevents this accidentally replacing the original version unless I deliberately change it to say ALTER.

Me, too. Having a CREATE OR ALTER statement does not give me a good feeling.




astruthers 75845
astruthers 75845
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 16
Great post. A minor point.
If you are using VCS, TFS, or some code repository this makes sense:
-- uspLogError logs error information in the ErrorLog table about the
-- These notes will NOT get persisted with the object
-- and will NOT be available to future developers scripting this object from SSMS
ALTER PROCEDURE [dbo].[uspLogError]
@ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted
AS -- by uspLogError in the ErrorLog table
BEGIN
SET NOCOUNT ON;
etc...
END
Problem is, quite often a developer will enherit a system, and the code reporsitory may or may not be available.
When that happens, you might use SSMS to script the object, in order to get ther "source" code of the objects.
In your example, those notes that come BEFORE the ALTER\CREATE statement are lost. They never got saved when the original object got created.
If you include all notes AFTER the ALTER\CREATE statement, they get persisted with the object, and would be available to a future developer scripting out those objects.
ALTER PROCEDURE [dbo].[uspLogError]
@ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted
AS -- by uspLogError in the ErrorLog table
-- uspLogError logs error information in the ErrorLog table about the
-- These notes will get persisted with the object
-- and will be available if this object gets scripted out in SSMS in the future
BEGIN
SET NOCOUNT ON;
etc...
END
In Summary:
Always write all notes for an object AFTER the ALTER\CREATE statement.

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)

Group: General Forum Members
Points: 975132 Visits: 49307
[quote]
astruthers 75845 - Thursday, March 14, 2019 2:24 PM
In your example, those notes that come BEFORE the ALTER\CREATE statement are lost.


That's not actually true if you're using SSMS. The comments before the Create statement WILL actually be included in the stored procedure. Try it yourself and see.

As to whether or not that's the correct way to do things or not, my take is that the first thing in a stored procedure should be the ALTER or CREATE statement.


--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
astruthers 75845
astruthers 75845
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 16
Jeff,
Just tested, and stand by what I said.
-- Comments before Create\Alter statement
CREATE PROCEDURE sp_Bletch
@BrandID varchar(6)
AS
-- Comments After Create\Alter statement
SELECT
[ID]
,[Name]
,[BrandID]
FROM [dbo].[BrandItems]
WHERE [BrandID] = @BrandID;
GO

Then, if you Right-Click on object, and "Script Procedure As" the external comments are not included.

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)SSC Guru (975K reputation)

Group: General Forum Members
Points: 975132 Visits: 49307
astruthers 75845 - Thursday, March 14, 2019 9:26 PM
Jeff,
Just tested, and stand by what I said.
-- Comments before Create\Alter statement
CREATE PROCEDURE sp_Bletch
@BrandID varchar(6)
AS
-- Comments After Create\Alter statement
SELECT
[ID]
,[Name]
,[BrandID]
FROM [dbo].[BrandItems]
WHERE [BrandID] = @BrandID;
GO

Then, if you Right-Click on object, and "Script Procedure As" the external comments are not included.

You tested this in SSMS? Interesting. I also tested before I posted and it worked as I said both in 2008 SSMS against an SQL Server 2008 installation and SSMS revs 17.2 and 17.91 against a 2016 SQL Server installation... what version of SSMS are you using?

Just in case there's a doubt, here's what I got when I did the very same thing you did to script the procedure back out...

/****** Object: StoredProcedure [dbo].[uspLogError] Script Date: 3/15/2019 12:28:54 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- uspLogError logs error information in the ErrorLog table about the
-- These notes will NOT get persisted with the object
-- and will NOT be available to future developers scripting this object from SSMS
CREATE PROCEDURE [dbo].[uspLogError]
@ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted
AS -- by uspLogError in the ErrorLog table
BEGIN
SET NOCOUNT ON;
Select 1;
END
GO

The comments where saved as a part of the stored procedure.


--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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