March 15, 2019 at 5:10 am
All our scripts have a CREATE that's executed if the sp doesn't exist followed by an ALTER. That way all the security permissions are preserved. We also sometimes have comments before the ALTER that document usage before the ALTER statement and these are kept on the installed stored procedure definition. If we decided to do a drop and create we would have to run a script to add all the security permissions to all the stored procedures and if you have over 1000 stored procedures on your database that would take a few minutes to run. So I've got a script that runs through all the stored procedures and on;y adds permissions to the sp's that haven't got the required permissions which normally takes about 1 second.
March 15, 2019 at 6:14 am
astruthers 75845 - Thursday, March 14, 2019 2:24 PMIn 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.
Comments are included if they're in the same batch as the CREATE. Every time you hit a GO, any comments that came before that are lost. They used to insist that comments had to be after the CREATE, until I explained this.
-- Comments here are not included with the PROC. The GO that follows the USE ends this batch.
USE [MyDatabase];
GO
--Comments here are included with the PROC, because they're in the same batch as the CREATE.
CREATE PROCEDURE dbo.[BlahBlahBlah]
AS
BEGIN
-- Comments embedded in the PROC are included, obviously. They could have between the CREATE and the AS,
-- between the AS and the BEGIN, even between the END and the GO.
END
GO
-- Comments here are not included with the PROC.
March 15, 2019 at 8:13 am
Jeff Moden - Thursday, March 14, 2019 10:35 PMastruthers 75845 - Thursday, March 14, 2019 9:26 PMJeff,
Just tested, and stand by what I said.
-- Comments before Create\Alter statementCREATE PROCEDURE sp_Bletch
@BrandID varchar(6)
AS
-- Comments After Create\Alter statement
SELECT
[ID]
,[Name]
,[BrandID]
FROM [dbo].[BrandItems]
WHERE [BrandID] = @BrandID;
GOThen, 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
GOSET 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.
I'd be interested to know the SSMS version as well. I tested it with several versions of SSMS from 12 to 17 and the comments were always saved with the stored procedure. Tried scripting it out a few different ways as well - create, modify, drop and create, etc. Always had the comments with both examples.
Sue
March 16, 2019 at 2:50 pm
jorgemor17 - Thursday, March 14, 2019 4:46 AMThanks 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 🙂
I couldn't agree more.
Effectively the Create or Alter game is like providing a nice flexible tool with good sharp blades and a powerful electric motor driving them, but it ws decided that having an earth lead or decent insulation or anny sort of proper guard around the blades was just a nuisance.
Tom
March 25, 2019 at 7:44 am
Steve, isn't it a tad ironic that you remind us that SQL is a set-based language, but your chosen example of a stored procedure runs counter to that philosophy.
Instead of passing back the new ErrorLog ID value as a parameter
INSERT [dbo].[ErrorLog]
(
[UserName] , [ErrorNumber] , [ErrorSeverity] ,
[ErrorState] , [ErrorProcedure] , [ErrorLine] , [ErrorMessage]
)
VALUES
(
CONVERT( sysname , CURRENT_USER ) , ERROR_NUMBER() , ERROR_SEVERITY() ,
ERROR_STATE() , ERROR_PROCEDURE() , ERROR_LINE() , ERROR_MESSAGE()
) ;
-- Pass back the ErrorLogID of the row inserted
SET @ErrorLogID = @@IDENTITY;
Why not use the OUTPUT clause to return the new record ID as part of a record set:
INSERT [dbo].[ErrorLog]
(
[UserName] , [ErrorNumber] , [ErrorSeverity] ,
[ErrorState] , [ErrorProcedure] , [ErrorLine] , [ErrorMessage]
)
-- Pass back the ErrorLogID of the row inserted
OUTPUT inserted.ErrorLogID
VALUES
(
CONVERT( sysname , CURRENT_USER ) , ERROR_NUMBER() , ERROR_SEVERITY() ,
ERROR_STATE() , ERROR_PROCEDURE() , ERROR_LINE() , ERROR_MESSAGE()
) ;
December 9, 2019 at 12:29 pm
Have also compiled a list of best practices for stored procedure out of my experience and wrote a blog. I hope one would find it handy and useful somewhere.
https://brahmanand.tech.blog/2018/07/17/best-practices-for-stored-procedures-in-sql-server/
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply